Extract text on right of various length from cells

[HammerD]

I have a list of cells with a "number" & description (i.e. - 260 SMALL
THINGS, 261 THINGS).
I want to just capture the text (of varying lengths) from the three digit
numbers.
I am making another column for just the numbers with the formula
"=LEFT(C1,3)" - but how can I capture just text?

Thanks in advance...

 

[Jacob Skaria]

If the data always have space between the numerics and text then try
=MID(A1,FIND(CHAR(32),A1)+1,LEN(A1))

If no space then try the below. Please note that this is an array formula.
Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"
=TRIM(MID(A1,COUNT(1*MID(A1,ROW($1:$9),1))+1,LEN(A1)))


If this post helps click Yes

 

[HammerD]

Sorry - my mistake... the cells have the text enclosed in parentheses. And I
want just the text - not the number or parentheses. There may be 3 or more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I also may note - when I receive e-mail notificatiion when a solution is
provided, the link does not work for me. So it took me a while to locate this
post.

 

[EricG]

This should work:

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

 

[Jacob Skaria]

Try

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

If this post helps click Yes

 

[Ron Rosenfeld]

Sorry - my mistake... the cells have the text enclosed in parentheses. And I
want just the text - not the number or parentheses. There may be 3 or more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I'm guessing that you want to retain the commas.

Here is a User Defined Function that will remove all digits, parentheses, and
extraneous spaces from your string; leaving you with the text, and the
separating commas.

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

=RemDigPar(A1)

(where A1 contains your string to process) in some cell.

=====================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = Application.WorksheetFunction.Trim _
(re.Replace(s, ""))
End Function
==============================
--ron

 

[Rick Rothstein]

And, for the OP's consideration, here is a non-RegEx UDF to do the same
thing...

Function RemDigitsPart(S As String) As String
Dim X As Long
Dim Parts() As String
Parts = Split(S, "(")
For X = 1 To UBound(Parts)
If X > 1 Then RemDigitsPart = RemDigitsPart & ", "
RemDigitsPart = RemDigitsPart & Split(Parts(X), ")")(0)
Next
RemDigitsPart = Replace(WorksheetFunction.Trim(RemDigitsPart), " ,", ",")
End Function

Ron: Note the final Replace function call on my UDF... I think you will need
to do that also (or the RegEx equivalent) since the WorksheetFunction.Trim
call will leave a space in front of a comma if there is one or more spaces
between the text and the closing parenthesis.

--
Rick (MVP - Excel)

Sorry - my mistake... the cells have the text enclosed in parentheses. And
I
want just the text - not the number or parentheses. There may be 3 or
more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I'm guessing that you want to retain the commas.

Here is a User Defined Function that will remove all digits, parentheses,
and
extraneous spaces from your string; leaving you with the text, and the
separating commas.

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

=RemDigPar(A1)

(where A1 contains your string to process) in some cell.

=====================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = Application.WorksheetFunction.Trim _
(re.Replace(s, ""))
End Function
==============================
--ron

 

[Ron Rosenfeld]

Sorry - my mistake... the cells have the text enclosed in parentheses. And I
want just the text - not the number or parentheses. There may be 3 or more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I'm guessing that you want to retain the commas.

Here is a User Defined Function that will remove all digits, parentheses, and
extraneous spaces from your string; leaving you with the text, and the
separating commas.

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

=RemDigPar(A1)

(where A1 contains your string to process) in some cell.

=====================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = Application.WorksheetFunction.Trim _
(re.Replace(s, ""))
End Function
==============================
--ron

Given Rick's (valid) concern about extraneous spaces that might not be taken
care of by the worksheetfunction.trim, I have change the regex pattern to take
care of that, and to also eliminate having to use worksheetfunction.trim at
all:

================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+|(^\s+)|(\s+$)|(\s(?=\W))"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = re.Replace(s, "")
End Function
===============================
--ron

 

[Rick Rothstein]

Heh, heh... you made me go back and look at my UDF again... and I don't
think I need the WorksheetFunction.Trim call either.

Function RemDigitsPart(s As String) As String
Dim X As Long
Dim Parts() As String
Parts = Split(s, "(")
For X = 1 To UBound(Parts)
If X > 1 Then RemDigitsPart = RemDigitsPart & ", "
RemDigitsPart = RemDigitsPart & Trim(Split(Parts(X), ")")(0))
Next
End Function

However, my UDF acts differently than yours for multiple spaces within the
text itself... I leave it there whereas you remove it. I can make the
argument for removing any extraneous outer spaces as the purpose of the UDF
seems to be to make a comma delimited list of the text located between the
parentheses and they would just "ugly up" the list; however, if there were
multiple internal spaces in the text, should we really be removing them (the
question being, why were they there in the first place... on purpose or by
mistake)? If the answer is yes, the multiple internal spaces should be
collapsed down to a single space, then in my code above, just swap out the
VBA Trim function for the Worksheet one. For the OP... that just means you
prepend WorksheetFunction. (notice the ending dot) onto the Trim function
call above.

--
Rick (MVP - Excel)

Sorry - my mistake... the cells have the text enclosed in parentheses.
And I
want just the text - not the number or parentheses. There may be 3 or
more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I'm guessing that you want to retain the commas.

Here is a User Defined Function that will remove all digits, parentheses,
and
extraneous spaces from your string; leaving you with the text, and the
separating commas.

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

=RemDigPar(A1)

(where A1 contains your string to process) in some cell.

=====================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = Application.WorksheetFunction.Trim _
(re.Replace(s, ""))
End Function
==============================
--ron

Given Rick's (valid) concern about extraneous spaces that might not be
taken
care of by the worksheetfunction.trim, I have change the regex pattern to
take
care of that, and to also eliminate having to use worksheetfunction.trim
at
all:

================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+|(^\s+)|(\s+$)|(\s(?=\W))"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = re.Replace(s, "")
End Function
===============================
--ron

 

[Rick Rothstein]

I don't think I need the WorksheetFunction.Trim call either.

I meant to say "or the Replace function call" also.

 

[Ron Rosenfeld]

Heh, heh... you made me go back and look at my UDF again... and I don't
think I need the WorksheetFunction.Trim call either.

Function RemDigitsPart(s As String) As String
Dim X As Long
Dim Parts() As String
Parts = Split(s, "(")
For X = 1 To UBound(Parts)
If X > 1 Then RemDigitsPart = RemDigitsPart & ", "
RemDigitsPart = RemDigitsPart & Trim(Split(Parts(X), ")")(0))
Next
End Function

However, my UDF acts differently than yours for multiple spaces within the
text itself... I leave it there whereas you remove it. I can make the
argument for removing any extraneous outer spaces as the purpose of the UDF
seems to be to make a comma delimited list of the text located between the
parentheses and they would just "ugly up" the list; however, if there were
multiple internal spaces in the text, should we really be removing them (the
question being, why were they there in the first place... on purpose or by
mistake)? If the answer is yes, the multiple internal spaces should be
collapsed down to a single space, then in my code above, just swap out the
VBA Trim function for the Worksheet one. For the OP... that just means you
prepend WorksheetFunction. (notice the ending dot) onto the Trim function
call above.

As frequently is the case, it depends on reading between the lines of the OP's
incomplete specifications.
--ron