# Getting a Error Message In Formula

#### CARBOB

##### Well-known Member
The formula in Cell GY2 works when the value in Cell Gu2 is in 3 different cells, but I'm trying to save space, It should be returning the value in Cell GV2. I get a error message. Where am I going wrong?
UNHIT COMBOS AFTER 1000 DRAWS.xls
GUGVGWGXGYGZHA
2000EEELLLOOO0=MCONCAT(IF(MOD(\$GU2,{1,2,3},2)=0,"E","O"))&MCONCAT(IF(GU2,{1,2,3},<5,"L","H"))&MCONCAT(IF(MOD(GU2,{1,2,3},8)<3,"O","I"))&(MOD(SUM(GU2,{1,2,3},10))*1
3001
4002
5003
6004
7005
8006
9007
10008
11009
COMBOS

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### GlennUK

##### Well-known Member
Show the working example too. Need to see what you are aiming for.

#### CARBOB

##### Well-known Member
Show the working example too. Need to see what you are aiming for.

This is the working example. I'm hoping to get the formulas to read the 3-digit numbers in column HA instead of each cell, to free up 2 columns. Thanks.
UNHIT COMBOS AFTER 1000 DRAWS.xls
GWGXGYGZHAHBHC
708/13/07919919OOOHLHOOO9
808/12/07233233EOOLLLOII2
908/11/0701010EOELLLOOO0
1008/10/07506506OEEHLHIOI5
1108/09/07989989OEOHHHOOO9
1208/08/07263263EEOLHLOII2
1308/07/07721721OEOHLLIOO7
1408/06/07443443EEOLLLIII4
15
16
17
18
COMBOS

#### Andrew Poulsom

##### MrExcel MVP
I'm not sure about the last bit because I can't see your working formula, but for a start try:

Code:
``=MCONCAT(IF(MOD((MID(\$GU2,{1,2,3},1)+0),2)=0,"E","O"))&MCONCAT(IF((MID(GU2,{1,2,3},1)+0)<5,"L","H"))&MCONCAT(IF(MOD((MID(GU2,{1,2,3},1)+0),8)<3,"O","I"))&MOD(SUM((MID(GU2,{1,2,3},1)+0)),10)``

Basically you need to use MID to extract the individual numbers.

#### CARBOB

##### Well-known Member
I'm not sure about the last bit because I can't see your working formula, but for a start try:

Code:
``=MCONCAT(IF(MOD((MID(\$GU2,{1,2,3},1)+0),2)=0,"E","O"))&MCONCAT(IF((MID(GU2,{1,2,3},1)+0)<5,"L","H"))&MCONCAT(IF(MOD((MID(GU2,{1,2,3},1)+0),8)<3,"O","I"))&MOD(SUM((MID(GU2,{1,2,3},1)+0)),10)``

Basically you need to use MID to extract the individual numbers.

Thanks Andrew, now I'm getting a #Value message.

#### Andrew Poulsom

##### MrExcel MVP
If G2 contains the text entry 989 my formula returns OEOHHHOOO6. You will get #VALUE if the entry in G2 is less than 3 characters.

#### CARBOB

##### Well-known Member
If G2 contains the text entry 989 my formula returns OEOHHHOOO6. You will get #VALUE if the entry in G2 is less than 3 characters.

I see that now. How do I get it to read the values 000 thru 099? I formatted the ceels as number, then I Custom formatted with 3 zeroes. That didn't work either.

#### Andrew Poulsom

##### MrExcel MVP
Format the cells as Text and remove the *1 from:

(GX7&GY7&GZ7)*1

#### CARBOB

##### Well-known Member
Thank You Mr. Poulsom, works great!!

Replies
1
Views
412
Replies
6
Views
1K
Replies
2
Views
303
Replies
5
Views
117
Replies
2
Views
168

1,191,420
Messages
5,986,464
Members
440,031
Latest member
davidvillegasr

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