tag:blogger.com,1999:blog-1869924468172210809.post4272340118250031952..comments2024-03-22T10:09:26.517+00:00Comments on Not Just Numbers: Excel Tip: A quick way to add a prefix or suffix to all cells in a listGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-1869924468172210809.post-13658877948144050412019-08-21T05:21:38.090+01:002019-08-21T05:21:38.090+01:00Well spotted, you're exactly right. Thanks for...Well spotted, you're exactly right. Thanks for that. I obviously had my VBA head on when I replied to that one! It should read:<br /><br />=TEXT(ROW(A1),"00")&". "&A1Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-54569274844725685362019-08-21T04:45:25.245+01:002019-08-21T04:45:25.245+01:00FORMAT is a vb function, not a worksheet function....FORMAT is a vb function, not a worksheet function. TEXT is the function you meant.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-79320320874120010122019-03-31T08:45:50.371+01:002019-03-31T08:45:50.371+01:00Try:
=FORMAT(ROW(A1),"00")&". &...Try:<br />=FORMAT(ROW(A1),"00")&". "&A1<br /><br />ROW returns the row number from A1 and the FORMAT function turns it into text using the number format "00", ensuring we have a leading zero if it is only a single digit.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-721573826861958612019-03-30T23:36:41.685+00:002019-03-30T23:36:41.685+00:00If I have eg. Lemons, Oranges, Grapes, Pineapples ...If I have eg. Lemons, Oranges, Grapes, Pineapples in A1, A2, A3 & A4, how do I add 01. to 04. so that it makes it 01. Lemons, 02. Oranges, 03. Grapes and 04. Pineapples ?Anonymoushttps://www.blogger.com/profile/15513229175920925955noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-31323587008510249512017-07-01T03:26:06.871+01:002017-07-01T03:26:06.871+01:00THANKS IT IS VERY USEFULTHANKS IT IS VERY USEFULBISMIhttps://www.blogger.com/profile/08647328556052076148noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-74708389166015905362015-09-29T20:10:03.043+01:002015-09-29T20:10:03.043+01:00@Brad
go back and read my comment again; cells ar...@Brad<br /><br />go back and read my comment again; cells are not being pasted elsewhere, it's doing exactly as you suggest, only quicker<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2142034991234819042015-03-04T20:46:48.932+00:002015-03-04T20:46:48.932+00:00@Jim,
Be careful do that! Any formulas which are ...@Jim,<br /><br />Be careful do that! Any formulas which are looking at the cell you need to copy and paste may be changed to refer to the cell which you dragged the data to. A safer option may be to just copy the cell and paste special values directly over the top.<br /><br />Brad.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91334718808262445472015-03-03T19:53:33.892+00:002015-03-03T19:53:33.892+00:00Thanks Jim. Yes that's a nice easy one when yo...Thanks Jim. Yes that's a nice easy one when you know how!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-69952517613499437442015-03-03T10:30:29.695+00:002015-03-03T10:30:29.695+00:00Quick way to and paste back a selected range as Va...Quick way to and paste back a selected range as Values is to drag an edge of the range away with the right mouse button pressed, drag it back again, THEN release the right mouse button and select "Copy Here as Values Only" from the shortcut menu that appears<br />It may sound like a palaver but it's really quick and easy<br /><br />JimAnonymousnoreply@blogger.com