SOLVED

Help needed with outcome from cell function into another concat function

%3CLINGO-SUB%20id%3D%22lingo-sub-1562113%22%20slang%3D%22en-US%22%3EHelp%20needed%20with%20outcome%20from%20cell%20function%20into%20another%20concat%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562113%22%20slang%3D%22en-US%22%3E%3CP%3EGoodafternoon.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20need%20to%20copy%2Fpaste%20a%20value%20from%20another%20website%20with%20the%20format%20%22%5BA1%5D%20%5BB2%5D%20%5BC3%5D%22.%3C%2FP%3E%3CP%3EIn%20the%20cells%20A1%2C%20B2%2C%20C3%20etc%20I%20have%20put%20some%20values%20which%20need%20searched%20and%20concaternated%2C%20with%20the%20substitute%20function%20I%20converted%20the%20copied%20value%20from%20the%20website%20into%20A1%3BB2%3BC3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20use%20this%20outcome%20to%20look%20into%20the%20given%20cells%20and%20concaternate%20them%3F%3C%2FP%3E%3CP%3EI%20tried%20using%20%3Dconcat(cell%20with%20outcome)%20but%20then%20it%20only%20shows%20A1%3BB2%3BC3%20instead%20of%20the%20values%20mentioned%20in%20the%20cells.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1562113%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562212%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20with%20outcome%20from%20cell%20function%20into%20another%20concat%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562212%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747605%22%20target%3D%22_blank%22%3E%40MUBCAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESet%20the%20number%20format%20of%20the%20target%20cell%20to%20General%20and%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%22%5B%22%26amp%3BA1%26amp%3B%22%5D%20%5B%22%26amp%3BB2%26amp%3B%22%5D%20%5B%22%26amp%3BC3%26amp%3B%22%5D%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562241%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20with%20outcome%20from%20cell%20function%20into%20another%20concat%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20But%20the%20copied%20value%20from%20the%20site%20daily%20changes.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20in%20the%20entire%20excel%2010%20colums%20and%205%20rows%20with%20values%20(from%20A1%20till%20J5).%20Those%20values%20in%20the%20mentioned%20cells%20are%20permanent.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EEvery%20day%20I%20copy%20the%20value%20from%20the%20site%20which%20always%20has%203%20mentioned%20codes%20in%20it%20(%5BA1%5D%2C%5BA2%5D%2C%5BA3%5D%2C%20%5BA1%5D%2C%5BB4%5D%2C%5BE5%5D%20etc.)%20but%20it%20changes%20on%20daily%20bases.%3CBR%20%2F%3ESo%20I%20only%20want%20to%20convert%20those%20copied%20value%20and%20want%20it%20automatically%20concaternate%20the%20values%20in%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20example%3A%3CBR%20%2F%3EA1%20%3D%2056%3C%2FP%3E%3CP%3EB2%20%3D%2078%3C%2FP%3E%3CP%3EC3%20%3D%2022%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20copy%20%5BA1%5D%2C%5BB2%5D%2C%5BC3%5D%20%26gt%3B%20converted%20it%20already%20to%20A1%3BB2%3BC3%20and%20want%20to%20concaternate%20it%20so%20I%20get%20the%20result%20of%20567822%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562465%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20with%20outcome%20from%20cell%20function%20into%20another%20concat%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747605%22%20target%3D%22_blank%22%3E%40MUBCAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20value%20A1%3BB2%3BC3%20is%20in%20cell%20A7.%20The%20following%20formula%20will%20concatenate%20the%20values%20of%20cells%20A1%2C%20B2%20and%20C3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDIRECT(LEFT(A7%2C2))%26amp%3BINDIRECT(MID(A7%2C4%2C2))%26amp%3BINDIRECT(RIGHT(A7%2C2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562509%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20with%20outcome%20from%20cell%20function%20into%20another%20concat%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562509%22%20slang%3D%22en-US%22%3EThanks%20Hans%2C%20this%20is%20exact%20what%20i%20was%20looking%20for!%20It%20worked!%20Many%20thanks%20for%20your%20fast%20help%20and%20solution%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Goodafternoon.


I need to copy/paste a value from another website with the format "[A1] [B2] [C3]".

In the cells A1, B2, C3 etc I have put some values which need searched and concaternated, with the substitute function I converted the copied value from the website into A1;B2;C3

 

How can I use this outcome to look into the given cells and concaternate them?

I tried using =concat(cell with outcome) but then it only shows A1;B2;C3 instead of the values mentioned in the cells.



Thanks in advance.

4 Replies
Highlighted

@MUBCAP 

Set the number format of the target cell to General and enter the formula

 

="["&A1&"] ["&B2&"] ["&C3&"]"

 

 

Highlighted

@Hans Vogelaar 

Thanks. But the copied value from the site daily changes. 

I have in the entire excel 10 colums and 5 rows with values (from A1 till J5). Those values in the mentioned cells are permanent. 

Every day I copy the value from the site which always has 3 mentioned codes in it ([A1],[A2],[A3], [A1],[B4],[E5] etc.) but it changes on daily bases.
So I only want to convert those copied value and want it automatically concaternate the values in the cell.

 

So in the example:
A1 = 56

B2 = 78

C3 = 22

 

I copy [A1],[B2],[C3] > converted it already to A1;B2;C3 and want to concaternate it so I get the result of 567822

Highlighted
Best Response confirmed by MUBCAP (New Contributor)
Solution

@MUBCAP 

Let's say the value A1;B2;C3 is in cell A7. The following formula will concatenate the values of cells A1, B2 and C3:

 

=INDIRECT(LEFT(A7,2))&INDIRECT(MID(A7,4,2))&INDIRECT(RIGHT(A7,2))

Highlighted
Thanks Hans, this is exact what i was looking for! It worked! Many thanks for your fast help and solution