Formula to separate numbers

sobrien1234

Board Regular
Joined
May 10, 2016
Messages
162
Office Version
  1. 365
Platform
  1. Windows
In column A I have number codes as follows:
1234/8
45673/18
4672/294

In column B I want formula which will isolate and provide me with the number on the right of the "/" so the answers in column B would be
8
18
294

I know I have use text to columns but need a formula that saves me doing that every time

thanks

Seamus

:confused:
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT(" ",500)),500))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Use B1 or C1 formula if you want results converted to Real numbers.
Use D1 or E1 formula if you want results as Text ( in case there are leading zeros that you want to retain ).

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">1234/8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">45673/18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">4672/294</td><td style="text-align: right;;">294</td><td style="text-align: right;;">294</td><td style="text-align: right;;">294</td><td style="text-align: right;;">294</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">123/0456</td><td style="text-align: right;;">456</td><td style="text-align: right;;">456</td><td style="text-align: right;;">0456</td><td style="text-align: right;;">0456</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet629</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">"/",A1</font>)+1,99</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A1,"/",REPT(<font color="Green">" ",99</font>)</font>),99</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">"/",A1</font>)+1,99</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1,"/",REPT(<font color="Purple">" ",99</font>)</font>),99</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks Alan - worked well - just had to add the backslash in and it was fine.

you're welcome
i though the / was it, don't know what happened to it
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,972
Members
409,848
Latest member
Blomsten
Top