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
Splitting Row Data
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Splitting Row Data
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
-
- Administrator
- Posts: 78440
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Row Data
Or use this formula in (for example) B2:
=--MID(A2,FIND("#",A2)+1,100)
and fill down.
=--MID(A2,FIND("#",A2)+1,100)
and fill down.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78440
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Row Data
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1487
- Joined: 05 Feb 2010, 22:25
Re: Splitting Row Data
The formula worked perfectly!! Thanks Hans!
-
- 3StarLounger
- Posts: 254
- Joined: 15 Aug 2016, 11:23
Re: Splitting Row Data
You can use FLASH FILL also.
-
- BronzeLounger
- Posts: 1487
- Joined: 05 Feb 2010, 22:25
Re: Splitting Row Data
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
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
-
- Administrator
- Posts: 78440
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Row Data
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)
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)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1487
- Joined: 05 Feb 2010, 22:25
Re: Splitting Row Data
Thanks Hans! As always it's perfect.