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

 

[Soggy]

I was trying to see if it was consistent in the hyphen other wise this should work.

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

But having said that if the UDF works go for that.

 

[Justin T]

I was trying to see if it was consistent in the hyphen other wise this should work.

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

But having said that if the UDF works go for that.

HI Soggy, thanks for your formula. it does the job, I am still getting the grasp of Mikes VBA. Every day is a school day

 

[Justin T]

Hi Mike, this is great. I know very little about VBA but this is a great learning experience. I did use the steps you advised but got only managed to extract on number (character). This is definitely a carbon based issue (myself not you). I will keep tinkering away. thanks Mike.

 

[Justin T]

I have another question to follow on from original question:

I have developed a formula to pull the number from the text string.
The number it brings back is : "1 01 20 10".
The number I would like is "112010".
Is there a formula/VBA code which can help in achieving the desired result?

Thanks

 

[bosco_yip]

I have another question to follow on from original question:

I have developed a formula to pull the number from the text string.
The number it brings back is : "1 01 20 10".
The number I would like is "112010".
Is there a formula/VBA code which can help in achieving the desired result? Thanks

Try this :

A1 : AAA-BBB-CC-1 01 20 10-DDD-EEEEEEEEEEEE-FFF-G4H-IIIIIIIIII

B1 array formula :

=NPV(-0.9,IFERROR(MID(SUBSTITUTE(A1," 0",),31-ROW($1:$30),1)/10,""))

Confirmed with Shift+Ctrl+Enter instead of Enter.

 

[Rick Rothstein]

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

Here is a more compact way to write your UDF...

Function GetNum(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X) Like "[1-9]*" Then
      GetNum = Split(Mid(S, X), "-")(0)
      Exit Function
    End If
  Next
End Function

 

[István Hirsch]

I have another question to follow on from original question:

I have developed a formula to pull the number from the text string.
The number it brings back is : "1 01 20 10".
The number I would like is "112010".
Is there a formula/VBA code which can help in achieving the desired result?

Thanks

=SUBSTITUTE(SUBSTITUTE(yourformula," 0","")," ","")