Prev: "Undo" creates bizarre behaviour
Next: Macro to change pivot field with latest value
From: Ryan D on 1 Mar 2010 13:29
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?
From: Lars-�ke Aspelin on 1 Mar 2010 13:37
On Mon, 1 Mar 2010 10:29:01 -0800, Ryan D
<RyanD(a)discussions.microsoft.com> 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
From: Rick Rothstein on 1 Mar 2010 13:52
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" <RyanD(a)discussions.microsoft.com> wrote in message
news:D7ED61D2-7913-4C6C-87B5-2EDB53F7C024(a)microsoft.com...
> 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?

From: Ron Rosenfeld on 1 Mar 2010 15:34
On Mon, 1 Mar 2010 10:29:01 -0800, Ryan D <RyanD(a)discussions.microsoft.com>
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
From: Chip Pearson on 1 Mar 2010 16:10
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
<RyanD(a)discussions.microsoft.com> 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?
 |  Next  |  Last
Pages: 1 2
Prev: "Undo" creates bizarre behaviour
Next: Macro to change pivot field with latest value