extract numbers from a string

D

Dave

Hello,

We import strings of numbers into a spreadsheet looking like
this:-

12Rev999,nextrev456
124Rev8888,nextrev1234

In practice the numbers can be any length from 1 to 4 numbers and there are
always three groups. I need to extract these numbers which are in Column A to
Column C
nearly forgot to mention the numbers in this format are text and I need them
as numbers.

Hope you can help.

D
 
L

Lars-Åke Aspelin

Hello,

We import strings of numbers into a spreadsheet looking like
this:-

12Rev999,nextrev456
124Rev8888,nextrev1234

In practice the numbers can be any length from 1 to 4 numbers and there are
always three groups. I need to extract these numbers which are in Column A to
Column C
nearly forgot to mention the numbers in this format are text and I need them
as numbers.

Hope you can help.

D

Assuming your strings are in column D you may try the following
formulas in cells A1, B1, and C1 respectivey:

=LEFT(D1,FIND("Rev",D1)-1)+0

=MID(D1,FIND("Rev",D1)+3,FIND("nextrev",D1)-FIND("Rev",D1)-4)+0

=RIGHT(D1,LEN(D1)-FIND("nextrev",D1)-6)+0

Hope this helps / Lars-Åke
 
D

Duke Carey

if you ALWAYS have "Rev" and ",nextrev" as the only alpha characters, then

=SUBSTITUTE(SUBSTITUTE(A1,"Rev",""),",nextrev","")*1

will work
 
O

Otto Moehrbach

Dave
If all else fails, this little macro will do it for you. I assumed your
data is in Column A starting in A1. This macro displays a message box
showing you the number for each entry in Column A. HTH Otto
Sub GetNums()
Dim rColA As Range
Dim i As Range
Dim c As Long
Dim Nums As Long
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
c = 1
Nums = 0
For c = 1 To Len(i)
If IsNumeric(Mid(i, c, 1)) Then
Nums = --(Nums & Mid(i, c, 1))
End If
Next c
MsgBox Nums
Next i
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top