search for 1st non * in text

[winde]

Hi

Can i know how to find the first non "*" in a cell and return all the text after that?


example:

*****ant --> ant

**Bat --> Bat

*********cat --> cat


Thanks.

 

[Rick Rothstein]

Assuming there will be no asterisks in the remaining text that you are after...

=SUBSTITUTE(A1,"*","")

 

[winde]

Assuming there will be no asterisks in the remaining text that you are after...

=SUBSTITUTE(A1,"*","")

Hi Rick

Problem is..there is..so i had some text like the following.

example:

******bat*en

I only need to remove all the "*" that is before the first letter.

Thanks.

 

[Rick Rothstein]

Hi Rick

Problem is..there is..so i had some text like the following.

example:

******bat*en

I only need to remove all the "*" that is before the first letter.

Will your text ever have any spaces in them?

 

[winde]

Will your text ever have any spaces in them?

yup. there are spaces in between.

i am trying to clean up the data which is in a mess now. and the first step is the remove the "*"s in front of the texts.

i'm looking at both using formula and vba. not sure which one is easier to apply to my data

 

[RoryA]

You could use something like:

=MID(A1,FIND(LEFT(SUBSTITUTE(A1,"*",""),1),A1),LEN(A1))

 

[winde]

You could use something like:

=MID(A1,FIND(LEFT(SUBSTITUTE(A1,"*",""),1),A1),LEN(A1))

Thanks! it worked.

Could you help explain the formula? I get how individual function works..but not when combining them together

 

[Rick Rothstein]

Thanks! it worked.

Here is the VBA version that you mentioned you might want to use...

[table="width: 500"]
[tr]
	[td]AfterLeadingAsterisks = Right(Range("A1"), Len(LTrim(Replace(Range("A1"), "*", " "))))[/td]
[/tr]
[/table]

 

[RoryA]

Sure.
SUBSTITUTE(A1,"*",""),
replaces all the asterisks with nothing. The LEFT function returns the first character of the result of that - which will therefore be the first non * character. The FIND function then looks for that character in the original string and MID returns everything from that point onwards. Does that help?

 

[winde]

Thanks both! Great help!