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),"^","")
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))
Last edited by Ingolf; 01-07-2016 at 12:50 AM.
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
Last edited by briguy22; 01-07-2016 at 01:51 AM.
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?
Last edited by Ingolf; 01-07-2016 at 03:20 AM.
Assuming the text in each cell will never be longer than 200 characters...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("^",A1)-1),"*",REPT(" ",200)),200))
Posting Permissions
Reply With Quote
Bookmarks