find first letters in a string of alphanumerics

[zpierucci]

I have a string ex: ".13 - LAR"

I would like to get the first letters, or non alphanumeric and non numeric characters of the string. They are not always the same distance from the beginning so I can't use left or right function. However they always begin in the 6th or 7th position.

Any thoughts?

 

[Scott Huish]

If it is only one hyphen as in your example:

=TRIM(MID(A1,FIND("-",A1&"-")+1,255))

Excel 2010
	A	B
1	.13 - LAR	LAR
2	.13-BMY	BMY
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND("-",A1&"-")+1,255))
B2	=TRIM(MID(A2,FIND("-",A2&"-")+1,255))

 

[DanteAmor]

Sorry. Yes, it is always a hyphen between numbers and texts

In Excel, the Text to Columns feature also can help you to split the cell content from one cell into two cells separately by the dash.


1. Select the data range that you want to split by dash.


2. Click Data > Text to Columns


3. In step 1 of the Convert Text to Columns Wizard, select Delimited option


4. And then click Next button, in step2 of this wizard, check Other option under Delimiters section, and enter the dash (-) symbol into the text box


5. And then click Finish button, the selected cells have been split into two columns by the dash

-----------------

Or also with these options:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

=REPLACE(A1,1,FIND("-",A1)+1,"")