Text String Extraction

[jbuchhorn]

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

 

[Special-K99]

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.

 

[jbuchhorn]

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.

 

[Special-K99]

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 ?

 

[JackDanIce]

@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.

 

[jbuchhorn]

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.

 

[Special-K99]

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.

 

[JackDanIce]

Based on examples provided, try:

=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

 

[FormR]

(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
	A	B
1	R6.300	6
2	R10.300	10
3	N10.300	10
4	R8	8
5	R8a	8
6	R10	10
7	R10a	10
8	R10b	10
Sheet2

Cell Formulas
Range	Formula
B1	=IFERROR(MID(A1,2,2)+0,MID(A1,2,1)+0)

 

[jbuchhorn]

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.