Solved

# Finding number string in text

Posted on 2003-03-10
Medium Priority
178 Views
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!
0
Question by:zoeg
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 3
• 2
• +2

LVL 6

Expert Comment

ID: 8102179
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.
0

LVL 43

Expert Comment

ID: 8102182
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
0

LVL 11

Expert Comment

ID: 8102336
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)

0

Expert Comment

ID: 8102347
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!!
0

Author Comment

ID: 8102386
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.

0

LVL 6

Expert Comment

ID: 8102400
Blank cell of what? Excel? DBGrid? HTML?

Thanks,

g.
0

Author Comment

ID: 8102408
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.

0

Author Comment

ID: 8102415
Yes in Excel. Sorry.
0

LVL 11

Expert Comment

ID: 8102429

- 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 ;)

0

Author Comment

ID: 8102448
Yes in Excel. Sorry.
0

LVL 11

Expert Comment

ID: 8102466
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)
0

Author Comment

ID: 8102477
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
0

Author Comment

ID: 8102488
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
0

Accepted Solution

andcu earned 560 total points
ID: 8102623
The above routine will put first seven chars of whatever is in column D into Column G on the same row.

Any questions or things u wanna change five us a shout

Public Sub mySub()

Dim i As Integer
Dim TheValue As String

MaxRow = 100

For i = 1 To MaxRow
TheValue = Left(Sheet1.Range("D" & CStr(i)).Text, 7)
Sheet1.Range("G" & CStr(i)).Value = TheValue
Next i

End Sub
0

Author Comment

ID: 8102973
Thank you so much! :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Iâ€™ve seen a number of people looking for examples of how to access web services from VB6.  Iâ€™ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web servâ€¦
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone â€¦
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applicâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month8 days, 15 hours left to enroll