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?

On Mon, 1 Mar 2010 10:29:01 -0800, Ryan D
wrote:

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?

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)

--
Rick (MVP - Excel)


"Ryan D" wrote in message
...
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?

On Mon, 1 Mar 2010 10:29:01 -0800, Ryan D
wrote:

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 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



On Mon, 1 Mar 2010 10:29:01 -0800, Ryan D
wrote:

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?

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

On Tue, 2 Mar 2010 21:39:16 -0000, "Project Mangler"
wrote:

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


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