tag:blogger.com,1999:blog-1869924468172210809.post8056625097392157460..comments2024-07-11T22:14:18.745+01:00Comments on Not Just Numbers: Excel Tip: Replacing parts of text stringsGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-1869924468172210809.post-52200189506774076862014-10-28T13:26:24.565+00:002014-10-28T13:26:24.565+00:00It is good practice in Excel to hold data in colum...It is good practice in Excel to hold data in columns. You can then have calculated columns to apply the same calculation to each row in the entered or imported columns.<br /><br />In the example above, the codes that needed editing would not just be in cell A1, but all the way down column A (depending how much data you have).<br /><br />There will likely be different data in columns B, C, etc. so let's say our calculated column will be in column D.<br /><br />The formula would be entered in column D on the first row (in the example above this is row 1 (as the formula is looking at A1), however in reality this will often be row 2, with the first row containing headers.<br /><br />Once the formula is entered in cell D1, we can copy or fill all of the way down column D. The row number in the formula will automatically update relative to its position, so, when we copy from D1 to D2, the reference in the function will move from A1 to A2. Say we have 100 rows of data, then by the time we get to cell D100, the first formula would read:<br /><br />=REPLACE(A100,4,3,"SAL")<br /><br />You might find the following two posts helpful:<br /><br />http://www.notjustnumbers.co.uk/2012/02/99-of-excel-users-get-this-wrong-how-do.html<br /><br />http://www.notjustnumbers.co.uk/2013/05/excel-tip-really-quick-way-to-copy-down.htmlGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-30917001640944030592014-10-28T12:56:10.115+00:002014-10-28T12:56:10.115+00:00Perhaps an example using the formulas on multiple ...Perhaps an example using the formulas on multiple cells would help. I'm not clear either on where the formula is entered or how it would apply to a colum of data.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2846942794774659582014-10-28T12:17:21.423+00:002014-10-28T12:17:21.423+00:00You're not being thick. Your approach works w...You're not being thick. Your approach works well for a one-off adjustment. But if you have 30,000 rows of data that all need editing ecery time you import them, the formula approach will save you a lot of time.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-26736681884250981862014-10-28T10:29:06.174+00:002014-10-28T10:29:06.174+00:00I realise I am probably being thick but in your ex...I realise I am probably being thick but in your example where do you enter this formula? Isn't it easier to just amend the text in A1 by clicking on it or using F2 key to retype the bit that needs changing?Anonymousnoreply@blogger.com