Remove first character from text string if it is an underscore

[alexander.small]

Hi, I have a list of thousands of Airports and some of them have an underscore before the name and in between the words.

Is there anyway that I can remove ONLY the underscore BEFORE the text string?
If there is an underscore inbetween words I need this to remain there.

I think this should be able to be done with a formula? As I only need the first character removed from the text string if it is an underscore.

Example of list:

_Menorca_Airport
San_Javier_Airport
Palma_de_Mallorca_Airport
Reus_Airport
_Santander_Airport
Santiago_de_Compostela_Airport
Seville_Airport
Tenerife_South_Airport
_Valencia_Airport
Valladolid_Airport
Zaragoza_Airport


Thanks in advance for your help!

Alex

[Ron Coderre]

With your sample list in A1:A11
this regular formula, copied down, removes the leading underscore character

Please Login or Register  to view this content.

Is that something you can work with?

[AlKey]

or this

=IF(LEFT(A1)="_",SUBSTITUTE(A1,"_","",1),A1)

[sktneer]

Moreover if you want to remove the first underscore, if any, in the original cells and don't want to extract the desired output in another column, try this code........

Please Login or Register  to view this content.

For detail see the attached sheet.

[alexander.small]

Thank you all for your help, each worked exactly as I wanted them to! And such quick responses as well, thanks again!