Return text string after the first alphabetical character

Klb1503

Board Regular
Joined
Jul 10, 2006
Messages
67
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this:-
Code:
[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
 
Last edited:
Upvote 0
Formula version:


Book1
AB
1| PRJ 001PRJ 001
2| -- WBS 002WBS 002
3| ---| WBS 003WBS 003
4|--||| WBS 004WBS 004
5|--|||--WBS 005WBS 005
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
This formula should also work...

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

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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