SOLVED

Dynamically edit only a portion of cell contents of a range

Copper 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 (Copper 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.

1 best response

Accepted Solutions
best response confirmed by RyeTo (Copper 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.

View solution in original post