Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Re: Concatenate worries

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?

Re: Concatenate worries

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


Re: Concatenate worries

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.


Re: Concatenate worries

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


Re: Concatenate worries

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.