Formula to extract numbers from text
I have text strings in the form
0' - Start of straight line, Road R-6 RD 0'
397' - Road R-5 RD 0', Start of straight line
9234' - Road R-29 RD 6247', End of road
6141' - Road R-1 RD 9233', Road R-29 RD 6239', End of road
739' - Road R-149 RD 0'
2534' - End of road, Road R-40 RD 2267'
0' - Start of straight line, Road R-1 RD 0', Road R-20 RD 0'
I need to extract the R- numbers and the RD values as shown in the attached file. Excel formula preferred but VBA is also acceptable.
Extract-number-and-values.xlsx
0' - Start of straight line, Road R-6 RD 0'
397' - Road R-5 RD 0', Start of straight line
9234' - Road R-29 RD 6247', End of road
6141' - Road R-1 RD 9233', Road R-29 RD 6239', End of road
739' - Road R-149 RD 0'
2534' - End of road, Road R-40 RD 2267'
0' - Start of straight line, Road R-1 RD 0', Road R-20 RD 0'
I need to extract the R- numbers and the RD values as shown in the attached file. Excel formula preferred but VBA is also acceptable.
Extract-number-and-values.xlsx
ASKER
Thanks for the formulas. Yes they will always be in pairs. I have two at the moment and I think that the maximum could be 4.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am just curious how this can be done using formula instead of UDF
Flora,
It is possible with formula, but the formula would be too lengthy, so if UDF does the job, why waste time with formula?
if you want this by formula, i suggest you open a new question.
It is possible with formula, but the formula would be too lengthy, so if UDF does the job, why waste time with formula?
if you want this by formula, i suggest you open a new question.
ProfJimJam is correct in that trying to do this in one long formula for the fourth instance (for example) would be too lengthy.
If you're happy to put up with some additional columns then this can be simplified somewhat as seen in the attached.
Hope that helps.
Extract-number-and-values--ss-.xlsx
If you're happy to put up with some additional columns then this can be simplified somewhat as seen in the attached.
Hope that helps.
Extract-number-and-values--ss-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's a pretty nifty solution. Nothing to add :-)
ASKER
Found a preferred solution.
Open in new window
and the following into C2 of your example and copy down
Open in new window
Columns D and E represent a second pair of R- and RD in the same data. Will they always occur in pairs? Is there a limit to the number of pairs per cell in column A?