Microsoft Excel 2010 - complete product...

If In say cell A1 has this text in it

J DKNX CVIX K101 - M DKNX CVIC P191

How could I get cell C1 to show only M DKNX CVIC P191

If they are separated by either - or —

I'm trying =mid(A1,find("-",a1)+1,20) but I can't seem to find a way to add it to look for both

✔ Best Answer

Here is one that is a bit more robust: =IF(ISERROR(RIGHT(A1,FIND(CHAR(151),A1))),RIGHT(A1,FIND(CHAR(45),A1)-2),RIGHT(A1,FIND(CHAR(151),A1)-1))

You may need to tweek it a bit, but it should get you what your looking for.

MIKE

Try this it will return everything after the "-" =RIGHT(A1,FIND(" -",A1))

It's working but I want it to to everything after a - or a —

They are different symbols and idea?

What is the second symbol? The first is a Dash or Hyphen, ASCII code 45.

But what is the second symbol?

It could be one of several possible codes:

ASCII Code 150 or 151 or possibly ASCII Code 6How did you input the character?

MIKE

Try this modified formula: =RIGHT(A1,FIND(

CHAR(151),A1))Change the CHAR() number to 150 or 6 if the above does not work.

MIKE

Here is one that is a bit more robust: =IF(ISERROR(RIGHT(A1,FIND(CHAR(151),A1))),RIGHT(A1,FIND(CHAR(45),A1)-2),RIGHT(A1,FIND(CHAR(151),A1)-1))

You may need to tweek it a bit, but it should get you what your looking for.

MIKE

Not tested it but i know Mike is a beast with formulas so im sure it will work, my forumla skills are very limited glad he was able to step in and help.

YOU GUYS ARE AMAZING! I Thought I was good at excel but 👌👌 you take the cake

End formula

=IF(ISERROR(RIGHT(A1,FIND(CHAR(151),A1))),RIGHT(A1,FIND(CHAR(45),A1)+1),RIGHT)I'm about to try integrate it with my program I'll let you know how it holds up

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History