Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
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(A 1))) If this post helps click Yes --------------- Jacob Skaria "HammerD" wrote: 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
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. "Jacob Skaria" wrote: 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(A 1))) If this post helps click Yes --------------- Jacob Skaria "HammerD" wrote: 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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
This should work:
=MID(A1,FIND("(",A1,1)+1,FIND(")",A1,1)-FIND("(",A1,1)-1) "HammerD" wrote: 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. "Jacob Skaria" wrote: 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(A 1))) If this post helps click Yes --------------- Jacob Skaria "HammerD" wrote: 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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
Try
=SUBSTITUTE(MID(A1,FIND("(",A1)+1,LEN(A1)),")",) If this post helps click Yes --------------- Jacob Skaria "HammerD" wrote: 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. "Jacob Skaria" wrote: 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(A 1))) If this post helps click Yes --------------- Jacob Skaria "HammerD" wrote: 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... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
On Wed, 1 Jul 2009 11:22:01 -0700, HammerD
wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
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) "Ron Rosenfeld" wrote in message ... On Wed, 1 Jul 2009 11:22:01 -0700, HammerD wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
On Wed, 01 Jul 2009 21:54:24 -0400, Ron Rosenfeld
wrote: On Wed, 1 Jul 2009 11:22:01 -0700, HammerD wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
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) "Ron Rosenfeld" wrote in message ... On Wed, 01 Jul 2009 21:54:24 -0400, Ron Rosenfeld wrote: On Wed, 1 Jul 2009 11:22:01 -0700, HammerD wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
I don't think I need the WorksheetFunction.Trim call either.
I meant to say "or the Replace function call" also. -- Rick (MVP - Excel) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract text on right of various length from cells
On Thu, 2 Jul 2009 00:40:20 -0400, "Rick Rothstein"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract just text from cells | Excel Worksheet Functions | |||
length of text string goes beyond cells are not visible | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |