Concatenate worries
Posted by Richard Jones on January 10, 2001 5:11 AM
When i download data from our accounts program and open it into excel - excel decides to remove the zero from the start of a reference if there is one there!
What i do at the moment is change the size of the cell so i can tell if there should be a zero at the start (all references are equal length) then concatenate a zero to the start of any that need it.
Is there a way of saying ...if charecters in a cell = 9 then put a 0 at the start of the cell???
or any other ways of saving me time
thanks
richard
Posted by Richard on January 10, 2001 5:15 AM
Further to this - how do i remove the first say three numbers from a reference?
Posted by Dave Hawley on January 10, 2001 5:23 AM
Hi Ricard
You could format the sheet as Text or Special>Zip code via Format>Cells>Number.
The drawback of course is all entries will be text and not numbers, but that can be overcome via code or formula.
Dave
- OzGrid Business Applications
Posted by Mark W. on January 10, 2001 5:44 AM
Richard, you can avoid your problem altogether if you'll open your file from within Excel using the File Open menu command and formatting your column at step 3 of 3 in the Text to Columns Wizard as Text. If your file is CSV change its extension to .txt before opening.
Posted by Aladin Akyurek on January 10, 2001 6:37 AM
If you mean 12345 --> 45, then use
B1 =IF(LEN(A1)>MaxLen,MID(A1,MaxLen,LEN(A1)),"") where MaxLen is say 3.
If A1 contains a number-formatted number, the above formula will turn it into a number that is text-formatted. If you so wish, you can avoid that by just replacing the MID-part of the above formula:
VALUE(MID(A1,MaxLen,LEN(A1))
Aladin
Posted by Mark W. on January 10, 2001 7:09 AM
The Text to Column Wizard can also take care of
this at the time the data file is opened.
