| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add spaces for specific fields to paste in
Thanks to both of you. This worked great.
"Pete" wrote: The middle part of this doesn't quite do what the OP asked for. I've assumed the data is in Sheet1 columns A to C with a header row so the data starts at A2. Insert a new worksheet and enter the following formula in A2: =Sheet1!A2&REPT(" ",19-LEN(Sheet1!A2)) In B2 you should enter the following formula: =IF(Sheet1!B2<0,REPT(" ",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&"-",REPT(" ",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&" ") And in C2 of Sheet 2 you should enter the following formula: =REPT(" ",6-LEN(Sheet1!C2))&Sheet1!C2 I wasn't sure if you wanted three separate fields, or for them to be joined together separated by a period. If the latter, you can enter the following in cell D2: =A2&"."&B2&"."&C2 Copy these formulae down for as many rows as you have data in Sheet 1. You can fix the values by selecting all the cells, Click <copy then Edit | Paste Special | Values | OK and <esc, then copy/paste into your accounting package. Alternatively, you can drag Sheet2 away from the main window and use File | Save As to give it a different name and a format to suit your other package, eg csv or dbf, so that you could then import it. Hope this helps Pete |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
| RECORDED MACRO PASTE'S DATE DIFFERENTLY TO MANUAL PASTE | Excel Worksheet Functions | |||
| paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
| I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
| Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
Threaded Mode
