# 1 or 0 based on Column Reference

#### SOLTEC

##### Board Regular
I have spreadsheet and I need to number default images with a 1 and all other with a 0(zero). The Column F has the values from 1:1 through 7000+:1. Any cell with a :1 is a default image and needs a 1 in the M Column. Any other number than :1 is a 0 (zero). Thank you for any and all assistance, it is greatly appreciated.

Here is the Spreadsheet cut

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### gaz_chops

##### Well-known Member
Are they formatted as Hours:Minutes?

#### gaz_chops

##### Well-known Member
Try this

=IF(RIGHT(B2,1)=1,1,0)

#### SOLTEC

##### Board Regular
No Gaz they're which image is primary and the 0's indicate a child that belongs to the primary.

#### SOLTEC

##### Board Regular

This just gave me all 0's all the way down. Did you notice that the column is not B, but F?

#### Rick Rothstein

##### MrExcel MVP
Try this

=IF(RIGHT(B2,1)=1,1,0)
That will fail if there can be 11 (or 21, 31, etc) images under a default image. Also, your test was wrong... you tested a piece of text against the number 1 instead of a text 1. For your formula, look at the last 2 characters and check for ":1". You can simplify the resulting formula like this...

=0+(Right(F2,2)=":1")

Note I used F2, not B2 as that was the column those values were in.

Last edited:

#### wynn89

##### Board Regular

Hi,

You may want to try this

=IF(RIGHT(B2,LEN(B2)-FIND(":",B2))="1",1,0)

Regards,
Wynn

#### SOLTEC

##### Board Regular
Rick,

Thank you, that did the trick. I appreciate your input and insight.

#### SOLTEC

##### Board Regular
Wynn,

The column is not B it is F, but thank you also for your input and time.

#### wynn89

##### Board Regular
Wynn,

The column is not B it is F, but thank you also for your input and time.

Hi,

Change the B to F,

that should work

Regards,
Wynn

Replies
5
Views
264
Replies
10
Views
148
Replies
17
Views
336
Replies
21
Views
171
Replies
9
Views
384

1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back