Solved Mid formula help on excel looking for 2 different symbols

Microsoft Excel 2010 - complete product...
May 6, 2016 at 06:53:45
Specs: Excel, Excel

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

See More: Mid formula help on excel looking for 2 different symbols

Report •


✔ Best Answer
May 6, 2016 at 10:32:55
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

http://www.skeptic.com/



#1
May 6, 2016 at 07:40:49
Try this it will return everything after the "-"

=RIGHT(A1,FIND(" -",A1))


Report •

#2
May 6, 2016 at 07:59:28
It's working but I want it to to everything after a - or a —
They are different symbols and idea?

Report •

#3
May 6, 2016 at 09:57:00
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 6

How did you input the character?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 6, 2016 at 10:22:02
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

http://www.skeptic.com/


Report •

#5
May 6, 2016 at 10:32:55
✔ 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

http://www.skeptic.com/


Report •

#6
May 6, 2016 at 11:31:21
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.

Report •

#7
May 6, 2016 at 12:32:07
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


Report •


Ask Question