Return text string after the first alphabetical character

[Klb1503]

Hi,

I have a file that exports out of a system as text, the format of the lines are all slightly different and will show as:

| PRJ 001
| -- WBS 002
| ---| WBS 003
|--||| WBS 004
|--|||--WBS 005


I would like to trim everything to the left of the first letter but I am struggling to find a way to do this
Is anyone able to help please?

Thanks
Karen

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG20Mar41
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dn.Value = Replace(Dn.Value, "-", " ")
    Sp = Split(Dn.Value, " ")
    Dn.Value = Sp(UBound(Sp) - 1) & " " & Sp(UBound(Sp))
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[wideboydixon]

Formula version:

Book1
	A	B
1	| PRJ 001	PRJ 001
2	| -- WBS 002	WBS 002
3	| ---| WBS 003	WBS 003
4	|--||| WBS 004	WBS 004
5	|--|||--WBS 005	WBS 005
Sheet1

Cell Formulas
Range	Formula
B1	{=MID($A1,MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},UPPER($A1)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),LEN($A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Rick Rothstein]

This formula should also work...

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"|",""))

 

[Klb1503]

They both work great - thank you