Extract value between text and character

lcm7721

New Member
Joined
Dec 5, 2018
Messages
7
Hello,
I need to extract the route number, which can be between 1 to 3 digits, see example below:

Route 1-1 South
Route 26-7 Ludlam
Route 29-2 West
Route A 101-1 North

As you can see the Route value and (-) dash character are constant.

Is there a formula in excel and/or PowerBi I can use to extract these values and get only the route numbers?

Expected results:
1
26
29
101

Thanks,
lcm
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,893
Office Version
2007
Platform
Windows
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:221.47px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Route 1-1 South</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Route 26-7 Ludlam</td><td style="text-align:right; ">26</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Route 29-2 West</td><td style="text-align:right; ">29</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Route A 101-1 North</td><td style="text-align:right; ">101</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=RIGHT(SUBSTITUTE(REPLACE(A1,FIND("-",A1),100,"")," ", REPT(" ",99)),99)+0</td></tr></table></td></tr></table>
 

lcm7721

New Member
Joined
Dec 5, 2018
Messages
7
Perfect, thank you!!!


Try this

AB
1Route 1-1 South1
2Route 26-7 Ludlam26
3Route 29-2 West29
4Route A 101-1 North101

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:221.47px;"><col style="width:76.04px;"></colgroup><tbody>
</tbody>

CellFormula
B1=RIGHT(SUBSTITUTE(REPLACE(A1,FIND("-",A1),100,"")," ", REPT(" ",99)),99)+0

<tbody>
</tbody>

<tbody>
</tbody>
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,449
or

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([src], " ", "-", {1, RelativePosition.FromEnd}, 0), type text)
in
    Extract[/SIZE]
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,893
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,482
Members
406,765
Latest member
FLCL

This Week's Hot Topics

Top