Concatenate capitals

[davemojo82]

Hi all,

Can anyone tell me if there is a way to concatenate just the capital letters from cells on a row?

I have a list of our product range and options and wanted to easily make part codes using the capital letters from the relevant boxes.

Any help would be much appreciated.

Kind regards,

Dave

[NBVC]

Can you give examples?

[davemojo82]

Certainly...

Cell 1
Single Glazed

Cell 2
Heavy Duty

Cell 3
STD

Cell 4
Fixed Pane

Ideally the CONCATENATE would gather all the capitals from the above cells and come back with...

SG/HD/STD/FP

Kind regards,

Dave

[jazaddict]

If you're willing to use VBA the LEFT function might help.
Lets say Cell B3 has "Raleigh".
LEFT(cells(3,2),1) will return "R"

Edit: Oops...Now that I see your example my suggestion doesn't help much

[NBVC]

If you download and install a free addin called Morefunc.xll from here... you can use formula:

=MCONCAT(REGEX.SUBSTITUTE(A1:A4,"[^A-Z]",""),"/")

where A1:A4 has your data...


Note: the addin is embeddable in your sheet so you can share it.

[davemojo82]

What if I were to CONCATENATE the cells into one cell then have another cell take the capitals from the CONCATENATED cell to return the partcodes?

Kind regards,

Dave

PS. Not too hot on VBA but will give it a go, cheers

[NBVC]

What if I were to CONCATENATE the cells into one cell then have another cell take the capitals from the CONCATENATED cell to return the partcodes?

Kind regards,

Dave

PS. Not too hot on VBA but will give it a go, cheers

See my last post for a suggestion... no VBA...