Excel : Isolating a number string from the midst of a free comments fi 2

[jaypoole]

I am trying to find a formula that will look at the contents of a cell (which will contain free text comments), in order to isolate any instance of a 1+ digit number and to return that number value whether it be 1 digit in length, 5 digits long or whatever. If the comments contain two separate number strings, then I want it to return two separate values - one for each etc.

Anyone know if this is possible.

thanks,

Jay

 

[fkegley]

I suspect you will have to write a macro to tackle this problem. The macro will need to examine each character in the cell until it finds a digit. It will then have to concatenate that character to a result field and continue examining and concatenating characters until it either runs out of that patch of digit characters or characters in the cell. I would suggest you put some sort of separator character at the end of each set of digits. That way you can further separate them after the macro is finished.

Frank Kegley
fkegley@hotmail.com

Frank kegley
fkegley@hotmail.com

 

[xlbo]

Ok - copy and paste this into a module (In VBEditor, goto Insert>Module)

Function GetNos(rng As Range)
GetNos = ""
ctr = 0
testtext = rng.Text
For i = 1 To Len(testtext)
testChar = Asc(Mid(testtext, i, 1))
If testChar > 47 And testChar < 58 Then
If i - 1 = ctr Or Len(GetNos) = 0 Then
GetNos = GetNos & Mid(testtext, i, 1)
ctr = i
Else
GetNos = GetNos & &quot;,&quot; & Mid(testtext, i, 1)
ctr = i
End If

Else
'not number

End If
Next i
End Function

Then, in your worksheet, type
=Getnos(A1)
where A1 holds your textfield that you want to extract the numbers from

If there are 2 discreet sets of numbers, the format will be
123,456 etc etc

HTH Rgds
~Geoff~

 

[DaleWatson123321]

Jay,

I've taken up your challenge of doing it via formula.

The model I've created currently allows for up to 13 characters, for each of the two numbers.

It's just a matter of copying the formulas down for each row of text.

It works regardless of whether the numbers contain formatting - e.g. $9,999,999.99. Thus, this number has 13 characters and would (currently) be the maximum allowed. But it's real easy to add for additional characters.

If you'd like the file, just email me and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Jay,

Perhaps I should have added that my formula model provides for:

1) The numbers are separated into their own cells (columns).

2) The numbers are converted to &quot;real numbers&quot; that you can format as you prefer.

3) If there is only 1 number in the text, the other cell is left &quot;blank&quot;.

4) If there are no numbers in the text, then both cells are left &quot;blank&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca