Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Finding number string in text

Posted on 2003-03-10
15
Medium Priority
?
181 Views
Last Modified: 2010-04-07
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
Comment
Question by:zoeg
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 6

Expert Comment

by:graham_charles
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

by:TimCottee
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

by:rdrunner
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Expert Comment

by:andcu
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

by:zoeg
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.

Please help ASAP.
0
 
LVL 6

Expert Comment

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

Thanks,

g.
0
 

Author Comment

by:zoeg
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.

Please help ASAP.
0
 

Author Comment

by:zoeg
ID: 8102415
Yes in Excel. Sorry.
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 8102429
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 ;)

0
 

Author Comment

by:zoeg
ID: 8102448
Yes in Excel. Sorry.
0
 
LVL 11

Expert Comment

by:rdrunner
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

by:zoeg
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

by:zoeg
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

by:
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

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question