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 Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History