Splitting Row Data

Leesha
5StarLounger
Posts: 1069
Joined: 05 Feb 2010, 22:25

Splitting Row Data

Post by 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
PlatinumLounger
Posts: 4270
Joined: 31 Aug 2016, 09:02

Re: Splitting Row Data

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

User avatar
HansV
Administrator
Posts: 69459
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting Row Data

Post by HansV »

Or use this formula in (for example) B2:

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

and fill down.
Regards,
Hans

User avatar
HansV
Administrator
Posts: 69459
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting Row Data

Post by 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.
Regards,
Hans

Leesha
5StarLounger
Posts: 1069
Joined: 05 Feb 2010, 22:25

Re: Splitting Row Data

Post by Leesha »

The formula worked perfectly!! Thanks Hans!

Toranaga
3StarLounger
Posts: 230
Joined: 15 Aug 2016, 11:23

Re: Splitting Row Data

Post by Toranaga »

You can use FLASH FILL also.