removing leading zeros from a text string

[Justin T]

Good Afternoon,
Long time reader, 1st time posting.

I have a text string, please see an example below:

AAA-BBB-CC-1 02 03 04-DDD-EEEEEEEEEEEE-FFF-G4H-IIIIIIIIII

I would like to extract the number at space 11 (only at space 11) with the leading zeros removed. Then return that value in a separate cell. I have used a succession of excel commands such as MID, CTRL F (find and Replace) to get the desired result.

Easy enough you, say! Lately, in the numbers at space 11, there are trailing zeros. These are required.

Is there a way I can run a formula/command to extract the number from space 11, remove the leading zeros but keep the trailing zeros and ignore any other numbers?

Thanks

 

[Special-K99]

I dont understand space 11, do you mean position 11 (there is no nunber at position 11, it's the hyphen in front of the 1)?
What number are you trying to extract in your example?
Can you give an example that contains leading zeroes, trailing zeroes as you've described.

 

[Justin T]

Hi Special-K99, firstly thanks for your response. Secondly, the number I am looking to extract is:
AAA-BBB-CC-1 02 03 04-DDD-EEEEEEEEEEEE-FFF-G4H-IIIIIIIIII. I should have been more precise, at position 12 is where the number I wish to extract begins. Does this make it easier for you to understand?

 

[Special-K99]

Can you give an example including leading and trailing zeroes, several examples would be better.

 

[Mike LH]

Hi,

If you don't mind losing the spaces try this

=LOOKUP(1E+100,--MID(SUBSTITUTE($A$2," ",""),SMALL(FIND({0,1,2,3,4,5,6,7,8,9},$A$2&"0123456789"),1),ROW(INDIRECT("1:"&LEN($A$2)))))

or with the spaces but note this assumes that the example you posted is truly representative.

=TRIM(MID(SUBSTITUTE("-" & A2&REPT(" ",6),"-",REPT(" ",255)),4*255,255))

 

[Soggy]

Does this always start at position 12 and end with a hyphen? If you can identify a consistant start/end point wheterh it starts after the 3rd Hyphen and ends at the 4th I think you could tweak a Mid formula.

 

[Justin T]

Hi Special -K99, thanks for the response. please see below:

AAA-BBB-CC- 4 15 01 10-CCC-DDD-EEE-F2G-HHHHHHHH-IIIIIIIII

Hope it helps.

 

[Justin T]

Hi Soggy, I hear ya mate! Unfortunately it does not have a consistent start point. it can be 11 or 12 or 13. Its not consistent. Dang it!

 

[Justin T]

Hi Mike, the example is as close to the true representation as possible. the Trim suggestions does provide me with a result that can be modified again. makes sense?! Thanks mate.

 

[Mike LH]

Hi Mike, the example is as close to the true representation as possible. the Trim suggestions does provide me with a result that can be modified again. makes sense?! Thanks mate.

Hi,

Manipulating strings like this often problematic because of subtle differences in the strings. This UDF sould do what you want including removing any leading zeroes. ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in on the right. Close VB editor.


Back on the worksheet call with this in a cell


=getnum(a2)

Note this can be dragged in the same way as any worksheet function.

Function getnum(rng As Range) As String
Dim x As Long
Dim Start As Long, nd As Long
For x = 1 To Len(rng.Value)
    If IsNumeric(Mid(rng.Value, x, 1)) And Mid(rng.Value, x, 1) <> "0" Then
        Start = x
        nd = InStr(x, rng.Value, "-", vbTextCompare)
        getnum = Mid(rng.Value, x, nd - Start)
        Exit Function
    End If
Next
End Function