Formula to grab letters in a cell to first number, Repalce, Substitute, Mid or Left?

[Joyner]

Hello, I am using the indirect function and the cell I am referencing contains Letters and numbers like the list below:

RPT1
RPT5a
WP2
WP10b
etc.

There are always letters first - always two or three letters, no spaces and then a number somtimes follows by a letter.

But what I always need in the set of the first two or three letters up to the number (the letters correspond to a named range), so in the examples above I need a formula to grab the "RPT", and "WP", or other combinations of letters.

I have searched and it seems there may be different possiblities with Replace or Substitute, or maybe the Mid or Left function, but I haven't found one to do what I need.

Any help is greatly appreciated.

Thanks

 

[gsistek]

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>RPT1</TD><TD>RPT</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>RPT5a</TD><TD>RPT</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>WP2</TD><TD>WP</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>WP10b</TD><TD>WP</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,2),LEFT(A1,3))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(VALUE(MID(A2,3,1))),LEFT(A2,2),LEFT(A2,3))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(VALUE(MID(A3,3,1))),LEFT(A3,2),LEFT(A3,3))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B4</TH><TD style="TEXT-ALIGN: left">=IF(ISNUMBER(VALUE(MID(A4,3,1))),LEFT(A4,2),LEFT(A4,3))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[T. Valko]

Hello, I am using the indirect function and the cell I am referencing contains Letters and numbers like the list below:

RPT1
RPT5a
WP2
WP10b
etc.

There are always letters first - always two or three letters, no spaces and then a number somtimes follows by a letter.

But what I always need in the set of the first two or three letters up to the number (the letters correspond to a named range), so in the examples above I need a formula to grab the "RPT", and "WP", or other combinations of letters.

I have searched and it seems there may be different possiblities with Replace or Substitute, or maybe the Mid or Left function, but I haven't found one to do what I need.

Any help is greatly appreciated.

Thanks

Try this...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

 

[pgc01]

Hi

If you know that it's always 2 or 3 letters, then (similar to gsistek):

=LEFT(A1,3-ISNUMBER(-MID(A1,3,1)))

 

[Joyner]

Hello, and thanks to everyone for the quick replies, they all work and return what I need.

Thanks again

 

[T. Valko]

Hello, and thanks to everyone for the quick replies, they all work and return what I need.

Thanks again

You're welcome. Thanks for the feedback!