Apr 03 2021 08:05 PM
I have a field which is essentially numeric with the possibility of a one character suffix. There can be 1, 2, or 3 numbers followed possibly by the alpha suffix
ex: result
num Suffix
2 2
3x 3 x
10 10
10w 10 w
114 114
115x 115 x
I would like to split this into two fields; one numeric, and one an alpha suffix field. I'm totally at a loss as to how to do it.
Apr 03 2021 08:44 PM
@NorskGrandpa Please see the attached workbook for two working examples. One with regular formulae and one using Power Query.
Apr 03 2021 08:58 PM
Apr 03 2021 09:38 PM
Alternative formulas:
=IF(ISNUMBER(--RIGHT(A2)),A2,--LEFT(A2,LEN(A2)-1))
=SUBSTITUTE(A2,C2,"")
Apr 03 2021 11:11 PM
Another alternative would be this...
To extract Numbers:
=IF(ISTEXT(A2),--LEFT(A2,LEN(A2)-1),A2)
To extract Suffix character:
=IF(ISTEXT(A2),RIGHT(A2),"")
Apr 03 2021 11:54 PM - edited Apr 04 2021 12:08 AM
Considering your data this will be the best possible solution:
=IF(ISNUMBER(E1),"",RIGHT(E1,1))
=SUMPRODUCT(MID(0&E1, LARGE(INDEX(ISNUMBER(--MID(E1, ROW(INDIRECT("1:"&LEN(E1))), 1)) * ROW(INDIRECT("1:"&LEN(E1))), 0), ROW(INDIRECT("1:"&LEN(E1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(E1)))/10)
N.B. Adjust cell references in the formula as needed.
I would like to suggest few UDFs (User Defined Functions), are VBA macro, which helps to extract Numbers as well Alphabets from any position regardless of their length.
Function PullNumbers(strText As String)
Dim i As Integer, strDbl As String
For i = 1 To Len(strText)
If IsNumeric(Mid(strText, i, 1)) Then
strDbl = strDbl & Mid(strText, i, 1)
End If
Next i
PullNumbers = CDbl(strDbl)
End Function
Function PullLetters(strText As String)
Dim x As Integer, strTemp As String
For x = 1 To Len(strText)
If Not IsNumeric(Mid(strText, x, 1)) Then
strTemp = strTemp & Mid(strText, x, 1)
End If
Next x
PullLetters = strTemp
End Function
How it works:
=PullLetters(E1)
6. Formula in cell G1:
=PullNumbers(E1)
You may adjust cell references in the formula as needed.