Greetings,
I have some text in a cell that is always laid out in the same format, but the length may be different ie:
aab*532*.3*-fox^
aaac*53268*.598*-car^
I am trying to find the text to the left of the "^" and to the right of the first "*" or to the right of the second "*" to the "^".
End results would need to be:
-fox
-car
Hi,
Give us more examples.
With given examples, use one of the following formulas:
=SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("-",A1)+1),"^","")
or
=SUBSTITUTE(RIGHT(A1,5),"^","")
Quote:
Originally Posted by Ingolf
aab*532*.3*apple^
aaac*53268*.598*-car^
george*ford*678*banana^
rifle*car*.705*@exclamation^
results would need to be:
apple
-car
banana
@exclamation
OK.
Try this formula:
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"*",REPT(" ",50)),30)),"^","")
or
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"^",""),"*",R EPT(" ",50)),30))
This works for this example. What would I do if I had a data set that contained more *'s or if there is information after the ^ that I do not want to keep:
aab*532*.3*apple^ROCK
aaac*53268*.598*-car^PAPER
george*ford*678*banana^CAR
rifle*car*.705*@exclamation^12345
results would need to be:
apple
-car
banana
@exclamation
First you would have to say thank you to that / those who tried to help.
If I come up with another formula, you will have to come up with other examples and you'll say, but if ...
It was hard, at first, come with significant examples?
Assuming the text in each cell will never be longer than 200 characters...Quote:
Originally Posted by briguy22
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("^",A1)-1),"*",REPT(" ",200)),200))