Splitting Row Data

[Leesha]

Hi,
I have an excel file that contains data in Column A that I need to split apart. This is an example of the data:
CT-118-1 - N Hartford Wal-Mart #5095
The only information the I need are the numbers at the end. The numbers will always follow the # and may vary in length.
Thanks!
Leesha

[YasserKhalil]

Try this UDF

Code: Select all

Sub Test()
    Debug.Print GetVal("CT-118-1 - N Hartford Wal-Mart #5095")
End Sub

Function GetVal(ByVal txt As String) As String
    Dim m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "#\d+"
        GetVal = Replace(.Execute(txt)(0), "#", "")
    End With
End Function

[HansV]

Or use this formula in (for example) B2:

=--MID(A2,FIND("#",A2)+1,100)

and fill down.

[HansV]

Or select column A.
On the Data tab of the ribbon, click Text to Columns.
In Step 1 of the Convert Text to Columns Wizard, select Delimited.
Click Next >.
In Step 2, click Other, then enter # in the box next to it.
Click Next >.
Select the first column, then select 'Do not import column (skip)'.
Click Finish.

[Leesha]

The formula worked perfectly!! Thanks Hans!

[Toranaga]

You can use FLASH FILL also.

[Leesha]

Hi,
I have a similar situation in an Excel report. I tried adjusting the code below without luck. In this situation, I need the last 4 numbers of each cell split off and it should be in number format, and may contain up to two decimals. For example CHWM2098 would equal 2098, SBWM5104.1 would equal 5104.1, SBWM0004 would equal 4.
Thanks!
Leesha

[HansV]

Let's say again that the data are in A2 and down.
Enter the following formula in B2, and confirm it with Ctrl+Shift+Enter to turn it into an array formula (this is essential!).
Then fill or copy down:

=--MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW($1:$20),1)),0),20)

[Leesha]

Thanks Hans! As always it's perfect.