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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,721
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Wookiee

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
Does this work for you? It assumes that each number in the Numbers-Numbers-Numbers is a single digit...

VBA Code:
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,153
Office Version
  1. 365
Platform
  1. Windows
An alternative means is to employ Power Query/Get and Transform found on the Data Tab

Mcode
Rich (BB code):
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
ABCD
1Column1Column1.1Column1.2
2TW-RE-4-2-0TW-RE-4-2-0
3XY-AB-8-9-7XY-AB-8-9-7
Sheet1
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,721
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,373
Messages
5,624,309
Members
416,020
Latest member
ANDREAC247

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top