# Help With Mconcat Formula

#### CARBOB

##### Well-known Member
Using the formula in Col "B" to read the 3-digit number in Col "A". Using 0,1,2,3,4= L and 5,6,7,8,9 = H B2 should be LLL,B3 LHH, B5 LHL
B6 LHH

Carbob
DOUBLESMIRRORSTOTALSMASTER A.xls
ABCD
2243LLL9
3459LLL18
4404LLL8
5394LLL16
6358LLL16
FILTERS

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### MrExcel MVP
=MCONCAT(IF(--(MID(A2,{1,2,3},1))<5,"L","H"))

#### Joe Was

##### MrExcel MVP
Do you mean:

CONCATENATE (text1,text2,...)

or

= A1 & B1

"MCONCAT" I never heard of?

##### MrExcel MVP
Hi Joe,

""MCONCAT" I never heard of?"

#### Joe Was

##### MrExcel MVP

Thanks I forgot about that addin. Only used "VSORT" from it, most of the others I just write my own code for.

#### CARBOB

##### Well-known Member
That worked great PaddyD, thanks. Someone else learned something too. LOL

Carbob

##### MrExcel MVP
=MCONCAT(IF(--(MID(A2,{1,2,3},1))<5,"L","H"))

=REGEX.SUBSTITUTE(REGEX.SUBSTITUTE(A2,"0|1|2|3|4","L"),"5|6|7|8|9","H")

CARBOB:

Why don't you invoke in D2:

=SUM((MID(A2,{1,2,3},1)+0))

#### CARBOB

##### Well-known Member
=MCONCAT(IF(--(MID(A2,{1,2,3},1))<5,"L","H"))

=REGEX.SUBSTITUTE(REGEX.SUBSTITUTE(A2,"0|1|2|3|4","L"),"5|6|7|8|9","H")

CARBOB:

Why don't you invoke in D2:

=SUM((MID(A2,{1,2,3},1)+0))

Thanks Aladin, I will do that.
Carbob

Replies
2
Views
78
Replies
11
Views
150
Replies
3
Views
88
Replies
1
Views
53
Replies
4
Views
43