extracting data

[mithu]

Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example

"Company XYX 23432 R & P analysis"

without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell

I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.

anyway to do a vlookup and have it search for a numerical value or
something like that?

anyhelp you can give would be greatly appreicated.

Thanks

mithu

 

[Madhan]

Hi, to extract the number alone, you have to iterate through the string,
check if each character is a number, if a character is a number then
concatenate it to another string. Repeat until you get an empty space, that
is after the entire company ID. I hope this helps you.

 

[Ron Rosenfeld]

Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example

"Company XYX 23432 R & P analysis"

without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell

I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.

anyway to do a vlookup and have it search for a numerical value or
something like that?

anyhelp you can give would be greatly appreicated.

Thanks

mithu

Since you are posting in the Programming Group, I assume you want a VBA
solution. Here is a UDF:

==================================
Option Explicit
Function ExtractDigits(str)
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"
ExtractDigits = oRegExp.Replace(str, vbNullString)
End With
End Function
=================================
--ron

 

[Stumbler]

Since we all have to start sometime: maybe not the quickest solution
but I hope understandable for newbies
Function ExtractNumber(StringIn As String) As String
Dim tmpString As String
Dim TheNumbers As String
'Create a string containing all numbers
TheNumbers = "0123456789"

Dim StartPos As Long
Dim NumLen As Long
Dim i As Long

For i = 1 To 10
StartPos = InStr(StringIn, Mid(TheNumbers, i, 1)) 'cycle
through the string to locate the first number
If StartPos > 0 Then Exit For ' if found
leave the for next
Next
StringIn = Mid(StringIn, StartPos) 'truncate
everything before the first number
For NumLen = 1 To Len(StringIn)
'look for the first character that isn't a number
StartPos = InStr(TheNumbers, Mid(StringIn, NumLen, 1))
If StartPos = 0 Then Exit For ' if found
leave the for next
Next
NumLen = NumLen - 1 'We are one
position to far, so substract 1
ExtractNumber = Mid(StringIn, 1, NumLen) 'return the
number
End Function

HTH,
Hans