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!
eg -
3000010 2G New Cell Nil 2 ID BTS Nil
I want to copy 30000010 to another cell.
Any help ASAP would be great!
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
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)
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!!
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.
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.
Thanks,
g.
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.
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.
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 ;)
- 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 ;)
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)
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").
wb.sheets("Table1").cells(
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
- 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
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
- 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much! :-)
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.