extracting data

  • Thread starter Thread starter mithu
  • Start date Start date
M

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
 
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.
 
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
Click to expand...

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
 
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
 
Back
Top