SOLVED

Deleting a portion of a formula for entire column

%3CLINGO-SUB%20id%3D%22lingo-sub-1551169%22%20slang%3D%22en-US%22%3EDeleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551169%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20figure%20out%20how%20to%20delete%20a%20portion%20of%20a%20formula%20for%20a%20column%20of%20a%20table.%20I%20know%20how%20to%20change%20the%20value%20of%20the%20cells%20by%20paste%20special%2C%20but%20is%20there%20a%20shortcut%20to%20delete%20parts%20of%20a%20formula%20for%20cells%20with%20only%20manual%20inputs.%20I%20have%20attached%20a%20picture%20below%20of%20my%20situation.%3C%2FP%3E%3CP%3E-%20in%20the%20below%20I'm%20looking%20to%20remove%20the%20%22*1.173)%2F1.173%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-07-28%20at%202.34.35%20PM.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208552i8A7AC9C35228E0A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202020-07-28%20at%202.34.35%20PM.png%22%20alt%3D%22Screen%20Shot%202020-07-28%20at%202.34.35%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1551169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551263%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742677%22%20target%3D%22_blank%22%3E%40Zmicrosoft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20Ctrl%2BH%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20444px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208558iF6456D7196E7F1C9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20Replace%20All%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551273%22%20slang%3D%22de-DE%22%3ESubject%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551273%22%20slang%3D%22de-DE%22%3EThere%20are%20many%20paths%20that%20lead%20to%20Rome.%20%3CBR%20%2F%3E%20I%20tried%20to%20describe%20one%20of%20these%20ways%20here.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Go%20to%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Start%20Search%20and%20find%20Replace%20Search%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20for%3A%20*%201.173)%20%2F1.173%20%3CBR%20%2F%3E%20Replace%20with%3A%20%22what%20you%20want%22%20%3CBR%20%2F%3E%20Press%20Find%20All%20and%20then%20Replace%20All.%20%3CBR%20%2F%3E%20Please%20specify%20the%20desired%20options%20beforehand%20(Button%20Options).%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20If%20the%20answer%20helped%20you%2C%20then%20please%20tick%20the%20correct%20answer%20so%20that%20others%20can%20also%20find%20out%20from%20it%2C%20a%20little%20like%20would%20also%20be%20good%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551300%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742677%22%20target%3D%22_blank%22%3E%40Zmicrosoft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Eselect%20cells%20containing%20those%20formulas%3CBR%20%2F%3Ereplace%20%3D%20with%20some%20other%20character%20(%20not%20number%20)%20for%20excample%20%22%7C%22%3CBR%20%2F%3Ereplace%20~**%20with%20nothing%20(%20empty%20)%3CBR%20%2F%3Ereplace%20%7C(%20with%20%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20excample%20first%20cell%3A%3C%2FP%3E%3CP%3Eoriginal%20formula%20%3A%3CBR%20%2F%3E%3D((25.995%2B0.9)*1.173)%2F1.173%3C%2FP%3E%3CP%3Eafter%20first%20replace%20%3A%3CBR%20%2F%3E%7C((25.995%2B0.9)*1.173)%2F1.173%20(%20no%20more%20formula%2C%20plain%20text%20)%3C%2FP%3E%3CP%3Eafter%20second%20replace%20%3A%3CBR%20%2F%3E%7C((25.995%2B0.9)%3C%2FP%3E%3CP%3Eafter%20third%20replace%20%3A%3C%2FP%3E%3CP%3E%3D(25.995%2B0.9)%20(%20formula%20again%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551314%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15762%22%20target%3D%22_blank%22%3E%40Olli%20Haavisto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20tried%20to%20find%20and%20replace%20with%20the%20%22%3CSPAN%3E*1.173)%2F1.173%22%20replacing%20it%20with%20)%2C%20it%20deleted%20the%20entire%20cell%20formula%20replacing%20it%20with%20just%20a%20closed%20parenthesis%2C%20but%20what%20you%20said%20worked.%20Thanks%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551366%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20any%20chance%20would%20you%20also%20happen%20to%20know%20how%20to%20use%20data%20that%20is%20laid%20horizontally%20in%20a%20formula%20that%20is%20filled%20down%20throughout%20a%20column.%20Or%20put%20another%20way%2C%20I%20want%20to%20fill%20a%20column%20with%20linked%20data%20from%20cells%20F11%2CG11%2CH11%2C%20etc%20rather%20than%20F11%2C%20F12%2C%20F13.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551517%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20a%20portion%20of%20a%20formula%20for%20entire%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742677%22%20target%3D%22_blank%22%3E%40Zmicrosoft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20into%20F12%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%2411%3A%2411%2C1%2CROW()-ROW(%24F%2411)%2BCOLUMN(%24F%2411))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I'm trying to figure out how to delete a portion of a formula for a column of a table. I know how to change the value of the cells by paste special, but is there a shortcut to delete parts of a formula for cells with only manual inputs. I have attached a picture below of my situation.

- in the below I'm looking to remove the "*1.173)/1.173"

 

Screen Shot 2020-07-28 at 2.34.35 PM.png

6 Replies

@Zmicrosoft 

Perhaps Ctrl+H

image.png

and Replace All

There are many paths that lead to Rome.
I tried to describe one of these ways here.

Go to

Start
Search and find
Replace
Search for: * 1.173) /1.173
Replace with: "what you want"
Press Find All and then Replace All.
Please specify the desired options beforehand (Button Options).


If the answer helped you, then please tick the correct answer so that others can also find out from it, a little like would also be good


Nikolino
I know I don't know anything (Socrates)
Best Response confirmed by Zmicrosoft (Occasional Contributor)
Solution

@Zmicrosoft 


select cells containing those formulas
replace = with some other character ( not number ) for excample "|"
replace ~** with nothing ( empty )
replace |( with =

 

for excample first cell:

original formula :
=((25.995+0.9)*1.173)/1.173

after first replace :
|((25.995+0.9)*1.173)/1.173 ( no more formula, plain text )

after second replace :
|((25.995+0.9)

after third replace :

=(25.995+0.9) ( formula again )

@Olli Haavisto 

When I tried to find and replace with the "*1.173)/1.173" replacing it with ), it deleted the entire cell formula replacing it with just a closed parenthesis, but what you said worked. Thanks 

@Sergei Baklan 

 

By any chance would you also happen to know how to use data that is laid horizontally in a formula that is filled down throughout a column. Or put another way, I want to fill a column with linked data from cells F11,G11,H11, etc rather than F11, F12, F13. 

@Zmicrosoft 

You may add into F12

=INDEX($11:$11,1,ROW()-ROW($F$11)+COLUMN($F$11))

and drag it down