Apr 22 2021 12:29 PM
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 |
Apr 22 2021 12:45 PM
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'.
Apr 22 2021 01:57 PM
Thank you Hans, works like a charm.
I will try with a formula and cell reference and see if it works as well.
Best
Apr 22 2021 02:04 PM
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.
Apr 22 2021 04:57 PM
Apr 23 2021 01:11 AM
You write 17.00P, but I see $17.00 in column A...
Apr 23 2021 05:10 AM
Sorry it should be 17
Apr 23 2021 05:13 AM
Apr 23 2021 06:06 AM - edited Apr 23 2021 12:05 PM
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)
Apr 23 2021 09:12 AM
Apr 23 2021 09:16 AM
Apr 23 2021 10:21 AM
Apr 23 2021 12:10 PM
SolutionMy 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.
Apr 23 2021 12:10 PM
SolutionMy 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.