SOLVED

Dynamically edit only a portion of cell contents of a range

%3CLINGO-SUB%20id%3D%22lingo-sub-2281860%22%20slang%3D%22en-US%22%3EDynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281860%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20wish%20to%20edit%20only%20certain%20segment%20or%20portion%20of%20cell%20contents%20of%20a%20range.%20For%20example%2C%20how%20can%20I%20edit%20the%20range%20dates%20(below)%20from%2020210521%20to%20(say)%2020210618%3F%20Without%20editing%20each%20cell%20individually%20one%20by%20one.%3C%2FP%3E%3CP%3EMuch%20appreciated.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3Eabc%7C20210521%7Cxyz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Eabc%7C20210521%7Capple%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Eorange%7C20210521%7Cabc%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Eabc%7C20210521%7Clong%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Esea%7C20210521%7Cabc%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283045%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034179%22%20target%3D%22_blank%22%3E%40RyeTo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20previous%20reply%20contained%20a%20typo%20-%20glad%20you%20were%20able%20to%20correct%20it%20yourself.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20FIND%20function%20returns%20the%20position%20of%20a%20specific%20string%20such%20as%20%22%7C%22.%20The%20%2B1%20serves%20to%20move%20to%20the%20right%20of%20that%20string.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282885%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282885%22%20slang%3D%22en-US%22%3EHans%2C%20I%20got%20it%20with%20a%20small%20modification%2C%20see%20below.%20I%20have%20to%20admit%20I%20still%20don't%20understand%20the%20%2B1%20and%20%2B1)%2B1)%2C100)%20and%20why%20the%20%26amp%3BTEXT%20comes%20before%20%26amp%3BMID%3F%3CBR%20%2F%3E%3DLEFT(B1%2CFIND(%22%7C%22%2CB1%2CFIND(%22%7C%22%2CB1)%2B1))%26amp%3BTEXT(A1%2C%220.00%22)%26amp%3BMID(B1%2CFIND(%22P%22%2CB1%2CFIND(%22%7C%22%2CB1%2CFIND(%22%7C%22%2CB1)%2B1)%2B1)%2C100)%3CBR%20%2F%3E17.00%20ALA.TO%7C20210618%7C19.00P%20ALA.TO%7C20210618%7C17.00P%3CBR%20%2F%3E16.00%20AQN.TO%7C20210618%7C18.00P%20AQN.TO%7C20210618%7C16.00P%3CBR%20%2F%3E90.00%20BMO.TO%7C20210618%7C100.00P%20BMO.TO%7C20210618%7C90.00P%3CBR%20%2F%3E75.00%20BNS.TO%7C20210618%7C70.00P%20BNS.TO%7C20210618%7C75.00P%3CBR%20%2F%3EThanks%20again.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282803%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282803%22%20slang%3D%22en-US%22%3ESorry%2C%20correction%20not%20B2%2C%20it%20should%20be%20C2%3A%3CBR%20%2F%3EI%20was%20looking%20for%20C2%20with%20AQN.TO%7C20210618%7C16.00P%20and%20so%20down%20the%20column%3F%3CBR%20%2F%3EWhich%20part%20of%20the%20string%20you%20provided%20replaces%2019.00%20in%20B1%20with%20A1%3F%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282799%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282799%22%20slang%3D%22en-US%22%3EHans%2C%20it%20didn't%20work%2C%20this%20is%20what%20I%20got.%3CBR%20%2F%3E17.00%20ALA.TO%7C20210618%7C19.00P%20ALA.TO%7C20210618%7C17.00P%3CBR%20%2F%3E16.00%20AQN.TO%7C20210618%7C18.00P%20ALA.TO%7C20210618%7C17.00P%3CBR%20%2F%3E90.00%20BMO.TO%7C20210618%7C100.00P%20ALA.TO%7C20210618%7C17.00P%3CBR%20%2F%3E75.00%20BNS.TO%7C20210618%7C70.00P%20ALA.TO%7C20210618%7C17.00P%3CBR%20%2F%3EI%20was%20looking%20for%20B2%20with%20AQN.TO%7C20210618%7C16.00P%20and%20so%20down%20the%20column%3F%3CBR%20%2F%3EWhich%20part%20of%20the%20string%20you%20provided%20replaces%2019.00%20in%20B1%20with%20A1%3F%3CBR%20%2F%3EThanks%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282543%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034179%22%20target%3D%22_blank%22%3E%40RyeTo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20in%20C1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLEFT(B1%2CFIND(%22%7C%22%2CB1%2CFIND(%22%7C%22%2CB1%3AB4)%2B1))%26amp%3BTEXT(A1%2C%220.00%22)%26amp%3BMID(B1%2CFIND(%22P%22%2CB1%2CFIND(%22%7C%22%2CB1%2CFIND(%22%7C%22%2CB1)%2B1)%2B1)%2C100)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282483%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282483%22%20slang%3D%22en-US%22%3EA%20B%3CBR%20%2F%3E1%2017.00%20ALA.TO%7C20210618%7C19.00P%3CBR%20%2F%3E2%2016.00%20AQN.TO%7C20210618%7C18.00P%3CBR%20%2F%3E3%2090.00%20BMO.TO%7C20210618%7C100.00P%3CBR%20%2F%3E4%2075.00%20BNS.TO%7C20210618%7C70.00P%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282481%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282481%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%3ESorry%20it%20should%20be%2017%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282280%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034179%22%20target%3D%22_blank%22%3E%40RyeTo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20write%2017.00P%2C%20but%20I%20see%20%2417.00%20in%20column%20A...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282064%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282064%22%20slang%3D%22en-US%22%3ECan%20I%20use%20%3DSUBSTITUTE%20to%20substitute%20a%20portion%20of%20cell%20with%20contents%20from%20another%20cell%3F%3CBR%20%2F%3EFor%20example%20substitute%20Column%20B%20prices%20(19.00P%20in%20first%20row)%20with%20Column%20A%20prices%20(17.00P%20in%20first%20row)%2C%20in%20cell%20B1%3F%3CBR%20%2F%3EA%20B%3CBR%20%2F%3E1%20%2417.00%20ALA.TO%7C20210618%7C19.00P%3CBR%20%2F%3E2%20%2416.00%20AQN.TO%7C20210618%7C18.00P%3CBR%20%2F%3E3%20%2490.00%20BMO.TO%7C20210618%7C100.00P%3CBR%20%2F%3E4%20%2475.00%20BNS.TO%7C20210618%7C70.00P%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281944%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034179%22%20target%3D%22_blank%22%3E%40RyeTo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20are%20in%20A2%20and%20down.%3C%2FP%3E%0A%3CP%3EIn%20(for%20example)%20B2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(A2%2C%2220210521%22%2C%2220210618%22)%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20the%20end%20of%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281928%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281928%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%3EThank%20you%20Hans%2C%20works%20like%20a%20charm.%3C%2FP%3E%3CP%3EI%20will%20try%20with%20a%20formula%20and%20cell%20reference%20and%20see%20if%20it%20works%20as%20well.%3C%2FP%3E%3CP%3EBest%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281875%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20edit%20only%20a%20portion%20of%20cell%20contents%20of%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034179%22%20target%3D%22_blank%22%3E%40RyeTo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20entire%20range.%3C%2FP%3E%0A%3CP%3EPress%20Ctrl%2BH%20to%20activate%20the%20Replace%20dialog.%3C%2FP%3E%0A%3CP%3EEnter%2020210521%20in%20the%20'Find%20what'%20box.%3C%2FP%3E%0A%3CP%3EEnter%2020210618%20in%20the%20'Replace%20with'%20box.%3C%2FP%3E%0A%3CP%3EClick%20'Options%20%26gt%3B%26gt%3B'.%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20the%20check%20box%20'Match%20entire%20cell%20contents'%20is%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20ticked.%3C%2FP%3E%0A%3CP%3EClick%20'Replace%20All'.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I wish to edit only certain segment or portion of cell contents of a range. For example, how can I edit the range dates (below) from 20210521 to (say) 20210618? Without editing each cell individually one by one.

Much appreciated.  

abc|20210521|xyz
abc|20210521|apple
orange|20210521|abc
abc|20210521|long
sea|20210521|abc
12 Replies

@RyeTo 

Select the entire range.

Press Ctrl+H to activate the Replace dialog.

Enter 20210521 in the 'Find what' box.

Enter 20210618 in the 'Replace with' box.

Click 'Options >>'.

Make sure that the check box 'Match entire cell contents' is not ticked.

Click 'Replace All'.

@Hans Vogelaar 

Thank you Hans, works like a charm.

I will try with a formula and cell reference and see if it works as well.

Best

@RyeTo 

Let's say the data are in A2 and down.

In (for example) B2, enter the formula

=SUBSTITUTE(A2,"20210521","20210618")

Fill down to the end of the data.

Can I use =SUBSTITUTE to substitute a portion of cell with contents from another cell?
For example substitute Column B prices (19.00P in first row) with Column A prices (17.00P in first row), in cell B1?
A B
1 $17.00 ALA.TO|20210618|19.00P
2 $16.00 AQN.TO|20210618|18.00P
3 $90.00 BMO.TO|20210618|100.00P
4 $75.00 BNS.TO|20210618|70.00P
Thanks again.

@RyeTo 

You write 17.00P, but I see $17.00 in column A...

A B
1 17.00 ALA.TO|20210618|19.00P
2 16.00 AQN.TO|20210618|18.00P
3 90.00 BMO.TO|20210618|100.00P
4 75.00 BNS.TO|20210618|70.00P

@RyeTo 

Try this in C1:

 

=LEFT(B1,FIND("|",B1,FIND("|",B1:B4)+1))&TEXT(A1,"0.00")&MID(B1,FIND("P",B1,FIND("|",B1,FIND("|",B1)+1)+1),100)

Hans, it didn't work, this is what I got.
17.00 ALA.TO|20210618|19.00P ALA.TO|20210618|17.00P
16.00 AQN.TO|20210618|18.00P ALA.TO|20210618|17.00P
90.00 BMO.TO|20210618|100.00P ALA.TO|20210618|17.00P
75.00 BNS.TO|20210618|70.00P ALA.TO|20210618|17.00P
I was looking for B2 with AQN.TO|20210618|16.00P and so down the column?
Which part of the string you provided replaces 19.00 in B1 with A1?
Thanks

Sorry, correction not B2, it should be C2:
I was looking for C2 with AQN.TO|20210618|16.00P and so down the column?
Which part of the string you provided replaces 19.00 in B1 with A1?
Thanks
Hans, I got it with a small modification, see below. I have to admit I still don't understand the +1 and +1)+1),100) and why the &TEXT comes before &MID?
=LEFT(B1,FIND("|",B1,FIND("|",B1)+1))&TEXT(A1,"0.00")&MID(B1,FIND("P",B1,FIND("|",B1,FIND("|",B1)+1)+1),100)
17.00 ALA.TO|20210618|19.00P ALA.TO|20210618|17.00P
16.00 AQN.TO|20210618|18.00P AQN.TO|20210618|16.00P
90.00 BMO.TO|20210618|100.00P BMO.TO|20210618|90.00P
75.00 BNS.TO|20210618|70.00P BNS.TO|20210618|75.00P
Thanks again.
best response confirmed by RyeTo (Occasional Contributor)
Solution

@RyeTo 

My previous reply contained a typo - glad you were able to correct it yourself.

 

The FIND function returns the position of a specific string such as "|". The +1 serves to move to the right of that string.