CODEWITHSUNDEEP
excel
pleasepleasehelpme
pleasepleasehelpme

Reputation: 5

Excel Text to Columns Formula

I am given the left column and want the right column to give the number in the left column or the highest number in the left column if there are two numbers given:

Before

I am using this equation to calculate the right column, given the left.

=RIGHT(B2,LEN(B2)-SEARCH("-",B2,SEARCH(" ",B2,1)+1))

This is what I want to display in the right column:

After

If anyone can help me fix that equation to display the number instead of value when there is only 1 number in the cell it would be much appreciated! Thank you!

Upvotes: 0

Views: 321

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

You can use this

=IFERROR(MID(B5,SEARCH("-",B5)+1,999),B5)

If you also want to return a value rather than a string, use this one, and format the cells as currency (as I think your left hand column already is)

=IFERROR(VALUE(MID(B2,SEARCH("-",B2)+1,999)),B2)

Upvotes: 0

atclaus
atclaus

Reputation: 1176

Use your same formula as you solved the harder part of the problem which is parsing out the higher value.

=IF(ISNUMBER(B2),B2,VALUE(RIGHT(B2,LEN(B2)-SEARCH("-",B2,SEARCH(" ",B2,1)+1))))

Upvotes: 1

Related Questions