Split Column by Digit to Non-Digit in Excel 2016

[Gos-C]

Hello,

I have a column with quantity having the unit of measure tacked on to it:

Column1
25g
50g
100g
25g
100g
100ml
25g
25g
25g
100g
10g
1kg

I can easily split the quantity and unit of measure, in Excel for Microsoft 365, using:

= Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"})

However, when I use the same M code in Excel 2016, it does not work.

Any help, please.

Thanks,
Gos-C

 

[Anthony47]

Maybe a UDF:
1) Copy the following code to a standard module of your vba project

Function VALnUnit(ByVal myStr As String) As Variant
Dim oArr(1 To 2)
For i = 1 To Len(myStr)
    If Asc(Mid(myStr, i, 1)) > 57 Then
        oArr(1) = CDbl(Left(myStr, i - 1))
        oArr(2) = Mid(myStr, i)
        Exit For
    End If
Next i
VALnUnit = oArr
End Function

2) Return to Excel; select B2:C2; set the following formula into the formula bar:

=VALnUnit(A2)

Copy B2:C2 down as necessary

 

[Gos-C]

Hi Anthony, thanks for the response and VBA code. I prefer to do the transformation in Power Query though, as I will be combining several price lists (each with hundreds of items) and doing other transformations as well. I will keep searching for a way to do it.

 

[jdellasala]

It appears you posted this to two Forums which is not allowed, and the post I replied to was taken down!
The function you noted in your post is a Power Query function and as you found not available in Excel itself.
To solve your problem, use this:

=SUBSTITUTE(SUBSTITUTE(A2,"g",""),"ml","")

If you need the result to be numeric instead of text, wrap it in VALUE.
The formula is easy enough to extend if more abbreviations turn up.

 

[Gos-C]

Hi jdellasala, I am sure that I posted this once only. I am requesting help for Power Query. I mentioned that the M code worked in Excel 365 Power Query but not in Excel 2016 Power Query.

 

[Sufiyan97]

Hi jdellasala, I am sure that I posted this once only. I am request help for Power Query. I mentioned that the code worked in Excel 365 Power Query but not in Excel 2016 Power Query.

I would suggest that, use that in title of the thread like, Power query, VBA for Formula
That will useful for specific helpers to directly provide solutions by that function and will save both parties time.

Like:
Power Query :Split Column by Digit to Non-Digit in Excel 2016
or
Split Column by Digit to Non-Digit in Excel 2016: Power Query

 

[jdellasala]

Hi jdellasala, I am sure that I posted this once only. I am requesting help for Power Query. I mentioned that the M code worked in Excel 365 Power Query but not in Excel 2016 Power Query.

Sorry, I misunderstood as did someone else on the other post. I can't say why it isn't working in Excel 2016. All I can suggest is to make sure you have the latest version. I don't know if this exists in that version, but in 365 (and I'm on the Insider edition), try going to File -> Account. If there's an Update Options button or drop down, click it and select Update Now.
As for the double posting, I am SURE I posted that answer to this question earlier today, and there was even a comment from one of the moderators that also thought that you thought you were trying to use the PQ code in Excel itself.
That said, I suspect that the Splitter.SplitTextByCharacterTransition function used in that step is the problem. I don't have the chops to figure out a work around!

 

[RoryA]

I would suggest that, use that in title of the thread like, Power query, VBA for Formula
That will useful for specific helpers to directly provide solutions by that function and will save both parties time.

Like:
Power Query :Split Column by Digit to Non-Digit in Excel 2016
or
Split Column by Digit to Non-Digit in Excel 2016: Power Query

Given that the OP posted in the Power Tools forum, there is no real need to specify in the title. Also, not really your place to suggest it.

 

[RoryA]

Sorry, I misunderstood as did someone else on the other post. I can't say why it isn't working in Excel 2016. All I can suggest is to make sure you have the latest version. I don't know if this exists in that version, but in 365 (and I'm on the Insider edition), try going to File -> Account. If there's an Update Options button or drop down, click it and select Update Now.
As for the double posting, I am SURE I posted that answer to this question earlier today, and there was even a comment from one of the moderators that also thought that you thought you were trying to use the PQ code in Excel itself.
That said, I suspect that the Splitter.SplitTextByCharacterTransition function used in that step is the problem. I don't have the chops to figure out a work around!

There was only this post. I deleted both your comment and the moderator's comment since they were not relevant and removed this thread from the unanswered posts list.

 

[RoryA]

However, when I use the same M code in Excel 2016, it does not work.

Just to note, you are correct that that Splitter function doesn't exist in 2016. I suspect it will require a custom M function. If you're lucky, someone like JGordon11 will spot this post.