Macro to turn text to a numbers without them becoming dates?

[tonywatsonhelp]

Hi Everyone

this seamed easy but total stuck thanks to excel being soooo clever!

I have a list of codes like this "TW-RE-4-2-0" i.e ="Letters-More Letters-Numbers-Numbers-Numbers"

now what i want to do is remove the Letters and the first two "-" so i'm left with "4-2-0" (Numbers-Numbers-Numbers")
but everytime i remove the letters excel turn whats left into a date 04/02/00

how can i so i need a macro to do this to all the cells with data in column J

please help if you can

Thanks

Tony

 

[Wookiee]

Does this work for you? It assumes that each number in the Numbers-Numbers-Numbers is a single digit...

Sub TonyWatsonHelp()

Dim rngCell As Range

For Each rngCell In Selection

   With rngCell.Offset(, 1)
   
      .NumberFormat = "#0,00"
      .Value = CLng(Right(Replace(rngCell, "-", ""), 3))
      
   End With
   
Next rngCell
      
End Sub

 

[alansidman]

An alternative means is to employ Power Query/Get and Transform found on the Data Tab

Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByPositions({0, 6}, false), {"Column1.1", "Column1.2"})
in
    #"Split Column by Position"

Book9
	A	B	C	D
1	Column1		Column1.1	Column1.2
2	TW-RE-4-2-0		TW-RE-	4-2-0
3	XY-AB-8-9-7		XY-AB-	8-9-7
Sheet1

 

[tonywatsonhelp]

Thank you to both of you,
the power query idea is good but i'm still not finding it relyable to use so maybe in the future.
Wookie, your idea will help so thank you