Please assist. Extract Number from String

  • Thread starter Thread starter Damil4real
  • Start date Start date
D

Damil4real

How can I use a macro to extract only the first batch of numbers from
the following type of info?

Examples:

01458-MODE
1548-JUNE
1245-NOD
01054-MORNING
00154-JUNE
55145-55145

Result should be:

01458
1548
1245
01054
00154
55145
--------------------

I only need the first batch of numbers before the "-" sign. There will
always be this sign "-" separating the two batch of data.

Thanks!
 
You don't specify

a) ranges
b) overwrite original or adjacent columns

Assuming say a range of A1:A6 and overwrite

With Range("A1:A6")
.Value = Evaluate("IF(ROW(" & .Address & "),LEFT(" & .Address &
",FIND(""-""," & .Address & ")-1))")
End With

that would however return the numbers as numbers - if you wish to return as
strings use ""'""&LEFT(...)
 
Assume the data is in column A. The code below extracts the digits left of
the hyphen and puts the results in column B, same row. Change colujhs to
suit.

Sub dk()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
Range("B" & i) = Left(Range("A" & i), InStr(Range("A" & i), "-") - 1)
Left
End Sub
 
This ought to work for you. Just adjust the range.

Option Explicit

Sub GetNumbers()

Dim rng As Range

For Each rng In Range("A1:A10")
If Not IsEmpty(rng.Value) Then
rng.Offset(0, 1).Value = Left(rng.Value, InStr(rng.Value, "-") -
1)
End If
Next rng

End Sub

Hope this helps! If so, let me know and click "YES" below.
 
FWIW
Some of these replies are good solutions, but unless you extract the left
part as TEXT and/or populate the target as cells(r?,c?).Text, Excel will
remove all leading zeros if the target cells aren't formatted as TEXT.

You could also do the same thing with a worksheet formula if you didn't want
to use VBA. Simply copy the formula to target cells as suits your need.

Kind regards,
Garry
 
Here's some code samples and a formula example:

Sub ExtractPrefixOnly(sRngAddress As String, lOffsetR As Long, _
lOffsetC As Long, Optional sDelimiter As String = "-")
' Extracts the left side of a delimited string.
' If prefix is numeric then it prepends the result with an apostrophe
' so leading zeros aren't lost.
Dim rng As Range
Dim iPos As Integer
Dim i As Integer
Dim sz As Variant

Set rng = ActiveSheet.Range(sRngAddress)
For i = 1 To rng.Cells.Count
iPos = InStr(1, rng.Cells(i).Text, sDelimiter, vbTextCompare)
If iPos > 0 Then
sz = Left(rng.Cells(i).Text, iPos - 1)
If IsNumeric(sz) Then sz = "'" & sz
rng.Cells(i).Offset(lOffsetR, lOffsetC) = sz
End If
Next
End Sub 'ExtractPrefixOnly()

'In sheet formula: (Column Absolute, Row relative)
'Assumes list is in columnA, target cell is Row1 of target column.
'ColumnB is what I used, but it could be used in any column (or columns).
'Revise to suit and copy where desired (ie: any row or any column[s}
other than source column)
'=IF(NOT(ISERROR(FIND("-",$A1)>0)),LEFT($A1,FIND("-",$A1)-1),"")

'Use example
Sub GetPrefixFromCells()
' Populates target cells according to their Row,Col offset from source cells.
' The source cells are a contiguous selection in ColumnA, though it can be a
single cell.
Const RowOffset As Long = 0 'Stay in the same row
Const ColOffset As Long = 2 'In this case, ColumnC
ExtractPrefixOnly Selection.address, RowOffset, ColOffset
End Sub 'GetPrefixFromCells()

HTH
Kind regards,
Garry
 
How can I use a macro to extract only the first batch of numbers from
the following type of info?

Examples:

01458-MODE
1548-JUNE
1245-NOD
01054-MORNING
00154-JUNE
55145-55145

Result should be:

01458
1548
1245
01054
00154
55145
--------------------

I only need the first batch of numbers before the "-" sign. There will
always be this sign "-" separating the two batch of data.

Thanks!
Click to expand...

If you don't need the leading zero's...

Sub Demo()
Debug.Print Val("01054-MORNING")
Debug.Print Val("00154-JUNE")
Debug.Print Val("55145-55145")
End Sub

Returns:
1054
154
55145

Perhaps format with leading zero's if you need to.

= = = = = = =
HTH :>)
Dana DeLouis
 
=left(a1,find("-",a1,1)-1)

assuming:
text to be extracted in A1
your letters start in column 2 (they do)

Good Luck!

Gabor Sebo
 
Back
Top