# Need A Formula

#### CARBOB

##### Well-known Member
Need a formula in Col E that will convert the digits in COL's B:D to S=1,4,7;C=0,6,8,9;O=2,3,5. The digits range from 0 to 9. All suggestions appreciated.
CASH3 MID 100GROUP COMBO SUMS WITH HORS.xlsx
ABCDE
1503/18/09850COC
1603/17/09218OSC
1703/16/09077OSS
HOR COMBOS

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
An extremely horrible and complex way of doing things, but may help you get by until someone comes along with a better formula (and there HAS to be one out there, I just can't figure it out):

=LOOKUP(B1,{0,1,2,3,4,5,6,7,8,9},{"C","S","O","O","S","O","C","S","C","C"})&LOOKUP(C1,{0,1,2,3,4,5,6,7,8,9},{"C","S","O","O","S","O","C","S","C","C"})&LOOKUP(D1,{0,1,2,3,4,5,6,7,8,9},{"C","S","O","O","S","O","C","S","C","C"})

I made a little table in A2:B11
0 C
1 S
2 O
3 O
4 S
5 O
6 C
7 S
8 C
9 C

then I used the following formula:
=VLOOKUP(C15,\$A\$2:\$B\$11,2)&VLOOKUP(D15,\$A\$2:\$B\$11,2)&VLOOKUP(E15,\$A\$2:\$B\$11,2)

Thanks, both of you did better than I could do!!!!

Replies
3
Views
298
Replies
2
Views
482
Replies
2
Views
923
Replies
4
Views
350
Replies
3
Views
2K

1,221,129
Messages
6,158,106
Members
451,465
Latest member
Big_Bill62

### 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.

### Which adblocker are you using?

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

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