Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
Hi,
I have a small problem. I have a text string: "1 - 7 of 61". I want to pick out the numbers in this text string. Also note that this text string is not constant and it keeps changing. For example, we have: "21 - 40 of 100". So I want to pick the numbers individually from each text string. Do anyone have a formula for that? Appreciate your help. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
On Mon, 24 Nov 2008 12:15:27 -0800 (PST), Harish
wrote: Hi, I have a small problem. I have a text string: "1 - 7 of 61". I want to pick out the numbers in this text string. Also note that this text string is not constant and it keeps changing. For example, we have: "21 - 40 of 100". So I want to pick the numbers individually from each text string. Do anyone have a formula for that? Appreciate your help. Thanks Will the numbers always be the first, third and last sequences, as in your examples? If so, then First number: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1) Second number: =LEFT(TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1), FIND(" ",TRIM(A1))+1),255)),FIND(" ",TRIM(MID(TRIM(A1), FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),255)))-1) Third number: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) Or, if the numbers will always consist of series of digits, and will be unsigned, you could write a short UDF. If the numbers might be signed, or might include a decimal, then the UDF would need a minor change. To do this <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. You can then use this formula: =GetNums(A1,Index) Where A1 contains the string, and Index is the position - 1 is the first set of digit(s), 2 is the second, and so forth. ================================= Option Explicit Function GetNums(str As String, Index As Long) Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\d+" Set mc = re.Execute(str) If mc.Count < Index Then GetNums = CVErr(xlErrNum) Else GetNums = CDbl(mc(Index - 1)) End If End Function =============================== --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
=LEFT(A1,FIND(" - ",A1)-1)
=MID(A1,FIND(" - ",A1)+3,FIND(" of",A1)-(FIND(" - ",A1)+3)) =RIGHT(A1,LEN(A1)-(FIND(" of",A1)+3)) For the first, second, and third numbers -- Gary''s Student - gsnu2007k "Harish" wrote: Hi, I have a small problem. I have a text string: "1 - 7 of 61". I want to pick out the numbers in this text string. Also note that this text string is not constant and it keeps changing. For example, we have: "21 - 40 of 100". So I want to pick the numbers individually from each text string. Do anyone have a formula for that? Appreciate your help. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
On Mon, 24 Nov 2008 15:52:39 -0500, Ron Rosenfeld
wrote: On Mon, 24 Nov 2008 12:15:27 -0800 (PST), Harish wrote: Hi, I have a small problem. I have a text string: "1 - 7 of 61". I want to pick out the numbers in this text string. Also note that this text string is not constant and it keeps changing. For example, we have: "21 - 40 of 100". So I want to pick the numbers individually from each text string. Do anyone have a formula for that? Appreciate your help. Thanks Will the numbers always be the first, third and last sequences, as in your examples? If so, then First number: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1) Second number: =LEFT(TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1), FIND(" ",TRIM(A1))+1),255)),FIND(" ",TRIM(MID(TRIM(A1), FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),255)))-1) Third number: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) If your strings are generally like your examples, you can return the second number with this simpler formula: =TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198,99)) --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
Select the column where your text strings are; click Data/Text To Columns on
Excel's menu bar. Select the Delimited option on the Step 1 dialog, then click Next. Put a check mark in the CheckBox labeled Space on the Step 2 dialog, then click Next. On the Step 3 dialog... change the Destination cell to the cell address you want the first split out number to go to, then click the 2nd column in the chart and select the OptionButton labeled "Do not import column (skip)", then do the same thing for the 4th column. Finally, click the Finish button. -- Rick (MVP - Excel) "Harish" wrote in message ... Hi, I have a small problem. I have a text string: "1 - 7 of 61". I want to pick out the numbers in this text string. Also note that this text string is not constant and it keeps changing. For example, we have: "21 - 40 of 100". So I want to pick the numbers individually from each text string. Do anyone have a formula for that? Appreciate your help. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying numbers froma text
Thanks for your ideas. I was trying to get the logic behind. I work
with an automated testing software which uses Visual Basic scripting and I was trying to incorporate your ideas by using Visual Basic functions. Thanks to everybody for their ideas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
identifying text associated with MIN value | Excel Discussion (Misc queries) | |||
Extracting Numbers froma Text String | Excel Worksheet Functions | |||
Identifying Part Numbers | Excel Worksheet Functions | |||
Dame: identifying duplicate numbers within a column | Excel Worksheet Functions | |||
identifying a string of positive numbers | Excel Worksheet Functions |