Using SUBSTITUTE with CONCATENATE Together in one formula

[AOGroups]

I am trying to join together 7 cells with a coma
In each cell I have to substitute Values

Cell A1 VALUE is Blue (need to subsitute to Bike)
Cell B1 VALUE is VALUE Red (need to subsitute to Pin)
Cell C1 VALUE is Yellow (need to subsitute to Sun)
Cell D1 VALUE is Orange (need to subsitute to Orange)
Cell E1 VALUE is Purple (need to subsitute to Flower)
Cell F1 VALUE is Green (need to subsitute to Grass)
Cell G1 VALUE is Brown (need to subsitute to Leaf)

I do not know the formula for SUBSTITUTE with CONCATENATE

I would like to have in 1 Cell
Bike, Pin, Sun, Orange, Flower, Grass, Leaf

[Keebellah]

Hi, welcome to the forum,
Version 7, be more specific please.
SUBSTITUTE and CONCATENATE are two entirely different things

You can simply start substitute the value for what you want it ad then add & "," & the next substitute etc etc.
The & is the same as concatenate

[AOGroups]

Thank you for your reply. I am using Office 365, but I also have Office 10

I wanted to know if I can use both formulas nesting together?

[Keebellah]

I do not know if both Functions exist in 2010
I have 2016 and this works

Please Login or Register  to view this content.

Bike,Pin

The result is

[AOGroups]

Thank you! I will try it now

[AOGroups]

Thanks, just tried it and it works!

[DJunqueira]

It seems to me that you are looking more for a =CONCAT(VLOOKUP(A1:A7,A1:B7,2,0)) formula.

[Keebellah]

You forgot the ',' between the values.

[DJunqueira]

You forgot the ',' between the values.

Ok

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

[AOGroups]

Thank you all so very much

[AOGroups]

I am trying to join together 4 cells with a coma
In each cell I have to substitute Values

Cell A1 VALUE is Blue (need to subsitute to Bike)
Several Values are in Cell A1 that need to be subsitute
Full time (need to subsitute to FT)
Part time (need to subsitute to FT)
Homemaker (need to subsitute to HMKR)

Cell B1 VALUE is VALUE Clerical only (Need to make be in Proper Case)
Cell C1 VALUE is sorting paper (Need to make be in Proper Case)
Cell D1 VALUE is retail (Need to make be in Proper Case)


I would like to have in 1 Cell

FT/Clerical Only/Sorting Paper/Retail