removing parts of functions...

SilverChewy · Apr 28, 2009

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CDamien%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CDamien%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CDamien%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Hi,

I've another problem and have found people here great at helping me solve them so I’m hoping this one is possible. I have a worksheet with 500 small "programs" that accepts data from a XML process them and then spit out the results to another worksheet, the problem is I have a rounding command in the formula that was needed but is now causing me a large headache. what I want to do it remove the fields in red without having to manually remove it from every formula (over 6000 entries)

I’ve had a look at the replace function to look into formulas but can’t get it to work, any ideas?

=ROUND(IF(M48=0,0,(L47/M48)),0)

Gerald Higgins · Apr 28, 2009

To use FIND and REPLACE to edit many formulas at the same time, here's one way.

First of all, use F&R to look for
=ROUND(
and replace with
ZZZ=

This will simply convert the formulas into text strings, using an additional text string that doesn't duplicate something you already have. It also removes the ROUND part.
The point of converting it to a text string is to suppress calculation of the worksheet while you go through the various stages.

Step 2, use F&R to find
,0)
and replace them with empty space (NOT space characters).

Step 3, use F&R to find
ZZZ=
and replace with
=

and that should do it (I think !)

SilverChewy · Apr 28, 2009

Thank you Ill try it out and let you know how I got on.

SilverChewy · Apr 29, 2009

Thanks Gerald,

I had to modify it a tiny bit as removing the =ROUND( worked fine however the removing the ,0) was and issue due to the fact that other functions used this feature. What I did to fix this is select only the columns that I needed to change and then under F&R i selected "Search" By columns.

Thanks again probable would have gone a bit mad removing them one at a time.

removing parts of functions...

SilverChewy

New Member

Excel Facts

Gerald Higgins

Well-known Member

SilverChewy

New Member

SilverChewy

New Member

Similar threads

Forum statistics

Share this page

removing parts of functions...

SilverChewy

New Member

Excel Facts

Gerald Higgins

Well-known Member

SilverChewy

New Member

SilverChewy

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock