Text String Extraction

jbuchhorn

New Member
Joined
Oct 17, 2017
Messages
5
Hi,

I have two related issues regarding the extraction of a portion of a text string which can be noted as 1) and 2) below.

1) In the case where I have some example inputs of R6.300, R10.300 & N10.300, I would like to be able to extract the numerical value proceeding the capital letter - in the case of my examples: 6, 10, 10 respectively. I have managed to make it work for each individual case (either N or R) but would ideally like a consistent formula that works for both (note that the number proceeding the letter ranges from single to double digit values only).

2) In the case where I have some example input R8, R8a, R10, R10a, R10b, I would like to be able to extract the numerical value proceeding the capital letter and terminates at either the end of the text, or the lower case letter - in the case of my examples: 8, 8, 10, 10, 10 respectively. I have managed to make it work for each individual (no letter following the number, a following the number, b following the number) but would ideally like a consistent formula that works for both (note that the number proceeding the capital letter ranges from single to double digit values only).

Any help would be greatly appreciated.

Cheers,

Jack
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
R6.300, R10.300 & N10.300

Is this data all in one cell or several?
If one cell is the ampersand part of the text?

...preceding not proceeding, and in any case the numerical value you're extracting succeeds the letter, it doesnt precede it.
 
Upvote 0
So the input cell will have either of those options in it. For my current formula, take cell C5 as having the input of R6.300 with the resultant of my formula being 6

=MID(C$5,SEARCH("R",C$5,1)+1,SEARCH(".",C$5)-SEARCH("R",C$5)-1)

I just need to work out how to modify this formula to work for both R and N.
 
Upvote 0
Still not clear. Read this slowly.

R6.300 R10.300 & N10.300

Will the cell ONLY contain ONE of those R/N codes. Or could there be more than one of those codes in the SAME cell ?
 
Upvote 0
@jbuchhorn I think providing 3 or 4 distinct examples of "before" and "after" will help

e.g.
If cell B2 = A then I want C2 to be B
If cell B2 = C then I want C2 to be D
If cell B2 = E then I want C2 to be F

Take into consideration when you explain a problem, you can see your spreadsheet in front of you, but anyone reading cannot, unless you provide a screenshot or very clear and precise examples.
 
Last edited:
Upvote 0
Sorry guys, I am quite new to this, I will do my best to try explain what I want.

For example, consider the following cells.

If Cell B2 = R6.300, than in Cell C2 I want 6 (text after "R" and before ".")
If Cell B3 = R10.360, than in Cell C3 I want 10 (text after "R" and before ".")
If Cell B4 = N12.200, than in cell C4 I want 12 (text after "N" and before ".")

On a much larger scale, I want all cells in column B to perform an operation to extract the text after "R/N" and before "." and place it in column C (i.e. B2 -> C2, B3 -> C3 etc.)

Let me know if this is still confusing and I shall try explain it another way.
 
Upvote 0
If there is ONLY once R/N value in the cell (a question which has still yet to be answered) you can use this

Try this

=IF(OR((LEFT(B2,1)="R"),LEFT(B2,1)="N"),INT(LOOKUP(9.9E+307,--LEFT(MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $B2&"1023456789")),999),ROW(INDIRECT("1:999"))))),B2)

If first character of B2 is R or N it extracts the integer of the number in the cell otherwuise returns B2.
 
Upvote 0
Based on examples provided, try:
Code:
=MID(B2,2,FIND(".",B2)-2)
And drag formula down.

You haven't given any examples if the text does not begin with R or N. Or any that relate to your second point in the initial post i.e. R8a, R10a etc, @Special K-99's suggestion I think accounts for this
 
Last edited:
Upvote 0
(note that the number proceeding the letter ranges from single to double digit values only).

Hi, based on this and the examples from post#1 here is another option.


Excel 2013/2016
AB
1R6.3006
2R10.30010
3N10.30010
4R88
5R8a8
6R1010
7R10a10
8R10b10
Sheet2
Cell Formulas
RangeFormula
B1=IFERROR(MID(A1,2,2)+0,MID(A1,2,1)+0)
 
Upvote 0
If there is ONLY once R/N value in the cell (a question which has still yet to be answered) you can use this

Try this

=IF(OR((LEFT(B2,1)="R"),LEFT(B2,1)="N"),INT(LOOKUP(9.9E+307,--LEFT(MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $B2&"1023456789")),999),ROW(INDIRECT("1:999"))))),B2)

If first character of B2 is R or N it extracts the integer of the number in the cell otherwuise returns B2.

Hi mate, yes that is correct! Each cell will only contain one value which will only include R or N.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top