Link to home
Start Free TrialLog in
Avatar of zoeg
zoeg

asked on

Finding number string in text

How can I find a string of numbers (7 numbers long) in a cell with text in it.

eg -

3000010 2G New Cell Nil 2 ID BTS Nil

I want to copy 30000010 to another cell.

Any help ASAP would be great!
Avatar of graham_charles
graham_charles

You neglected to say what kind of "cell" (Excel, from your terminology, or maybe an ActiveX grid control?), but if your data generally looks like the above then you could parse it with the Split function:

Dim aParse, wIndex As Integer

aParse = Split(MyData, " ")

If IsArray(aParse) Then
   For wIndex = LBound(aParse) To UBound(aParse)
      If IsNumeric(aParse(wIndex)) And (Len(wIndex) = 7) Then
         ' "copy aParse(wIndex) to another cell"
         Exit For
      End If
   Next
End If

Hope that helps,

g.
Hi zoeg,

Is the number always in the same part of the cell? If it is then you can easily get the first 7 characters:

MsgBox Left(CellText,7)

For example.

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com
Hello

Do you only want the "1st" Number , or do you want "ANY" number or "ANY NUMBER THATS 7 digits long" or "ANY NUMBER THATS UP TO 7 digits long"

Anyway... IF you know the EXACT position of the number, i would suggest left$(myString,7) (allways 7 digits?)

Or you can try (better Version - also catches 1-N Digit numbers)

'This will always output the 1st Number of the string (if the 1st element IS a number of course)
dim arLine() as string
arline = slpit ("3000010 2G New Cell Nil 2 ID BTS Nil", " ")
debug.? arline(0)

Or another option might be to use regular expressions ...
(If you dont know much about the number ... but nmeed more then one line as example)

You need to be more specific about the format of the data.  Will the numeric part come first?  You may have to parse getting Ascii number from the string or something.  More info please!!
Avatar of zoeg

ASKER

I am trying to get this macro to find and copy just the number (3000010 for example) to another cell.

Please help. How can I copy just the firt 7 characters (numbers) from a cell and then paste them in the next blank cell.

Please help ASAP.
Blank cell of what? Excel? DBGrid? HTML?

Thanks,

g.
Avatar of zoeg

ASKER

I am trying to get this macro to find and copy just the number (3000010 for example) to another cell.

Please help. How can I copy just the firt 7 characters (numbers) from a cell and then paste them in the next blank cell.

Please help ASAP.
Avatar of zoeg

ASKER

Yes in Excel. Sorry.
Well Like andcu already said... More info please ;)

- We need to know what kind of "cell" you are refering to so we know how to copy it there...
- We need to know more then 1 line of example date... Since we need to know - is the number allways 7 digits - Is the number allways seperated by a blank - Is there sometimes text at the beginning, etc...

Without these informations its kinda hard....

But you might try this code to extract the number (had a type in last Version):

dim arLine() as string
arline = split ("3000010 2G New Cell Nil 2 ID BTS Nil", " ")
debug.? arline(0)

This code ASSUMES several things :

- The number is allways 1st
- the number is allways seperated by a " " from the rest of the string

The code does NOT CHECK

- If the number has N (7) digits
- If its a number
- If there is at least SOMETHING to work with

To copy the number to the next cell we really need to know more ;)

Avatar of zoeg

ASKER

Yes in Excel. Sorry.
Ahh ... Excel...

So you already have a way to determine to current cell... How do you reference to it?

if you have a workbook object already (wb) then you can try this snippet

(make sure you have on error resume next in there)

'---
on error resume next
dim arField() as string
arfield() = split(wb.sheets("Table1").cells(1,1)," ")
wb.sheets("Table1").cells(1,2).value = arField(0)
Avatar of zoeg

ASKER

Basically

- Copy the first 7 digits (letters or numbers) of a cell in column D. They all have the same kind of stuff in them
EG - 3000010 2G New Cell Nil 2 ID BTS Nil
3000046 2G CAP UG Any 1 ID BTS Nil
3000078 2G SWAP UG 1 to 4 1 ID BTS Nil
 - Then find / goto the empty cell at the end of the row (eg - column I row 7) and paste the 7 digits copied from column D.

Thankyou
Avatar of zoeg

ASKER

Basically

- Copy the first 7 digits (letters or numbers) of a cell in column D. They all have the same kind of stuff in them
EG - 3000010 2G New Cell Nil 2 ID BTS Nil
3000046 2G CAP UG Any 1 ID BTS Nil
3000078 2G SWAP UG 1 to 4 1 ID BTS Nil
 - Then find / goto the empty cell at the end of the row (eg - column I row 7) and paste the 7 digits copied from column D.

Thankyou
ASKER CERTIFIED SOLUTION
Avatar of andcu
andcu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zoeg

ASKER

Thank you so much! :-)