Extract only text from column

  • Thread starter Thread starter Ryan D
  • Start date Start date
R

Ryan D

How do I extract just the text portion of a column? For example if column
contains:
"123 abcd", how do I get just the "abcd" in a new column?
 
How do I extract just the text portion of a column? For example if column
contains:
"123 abcd", how do I get just the "abcd" in a new column?
Click to expand...

Please give more information about the possible format of the first
column.
Is the "text portion" always preceeded by a space?
Is the "text portion" always the rightmost part?
etc
etc

Here is one formula that suits the given example, but only that

=IF(A1="123 abcd","abcd","no information given on how to handle "&A1)

Lars-Åke
 
Perhaps this UDF (user defined function) will do what you want...

Function GetTextOnly(S As String) As String
Dim X As Long
GetTextOnly = Space(Len(S))
For X = 1 To Len(S)
If Mid(S, X, 1) Like "[!0-9]" Then Mid(GetTextOnly, X) = Mid(S, X, 1)
Next
GetTextOnly = WorksheetFunction.Trim(GetTextOnly)
End Function

In case this is a new concept for you... copy/paste the above code into the
code window for a standard Module (Insert/Module from the VB Editor's menu
bar), then just use the GetTextOnly function on a worksheet just like you
would use any other worksheet function. For example, if your text is in A1,
you could put this in your "new column" cell...

=GetTextOnly(A1)
 
How do I extract just the text portion of a column? For example if column
contains:
"123 abcd", how do I get just the "abcd" in a new column?
Click to expand...

How to do it depends on how close to reality your example is.

If the text portion is always the last word, then a simple formula:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

On the other hand, if letters and digits can be interspersed throughout the
string: e.g.

123a6bc789d

then a UDF might be more appropriate.

You also need to specify what you want to do with characters that are neither
letters nor digits. e.g: <space>; punctuation; pluses and minuses, etc.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like
=GetText(a1)
in some cell.

As written, this UDF will eliminate everything that is not a letter in the
standard English alphabet; but the pattern can be easily modified.

=======================================
Option Explicit
Function GetText(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[^A-Za-z]" 'removes any non-letters
re.Global = True
GetText = re.Replace(s, "")
End Function
=====================================
--ron
 
Your question isn't entirely clear. If all you want to do is get all
of the text that follows the first space character (regardless of
whether the text to the left of the space is numeric) then you can use
a formula like

=MID(A1,FIND(" ",A1)+1,LEN(A1))

If you have further circumstances, post back with much more details
about the format and content of the text you want to break apart.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks Ron,

The UDF is a keeper.

As written, this UDF will eliminate everything that is not a letter in the
standard English alphabet; but the pattern can be easily modified.

=======================================
Option Explicit
Function GetText(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[^A-Za-z]" 'removes any non-letters
re.Global = True
GetText = re.Replace(s, "")
End Function
=====================================
--ron
Click to expand...
Click to expand...

Glad to help you. Thanks for the feedback.
--ron
 
Back
Top