Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
19th January 2008
Posts
3

Hi All,

I have a spreadsheet full of site codes which should be the format:
AA1111

or

A1111

Where A = any alpha character & 1 = any numeric character

unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

At the moment I manually correct this. Is there a better way to do this?

Thanks,

Pete
Last edited by Dave Hawley; August 7th, 2008 at 18:04.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Check String And Insert 0's Until String Is Correct Length

Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
 =IF(LEFT(A1)<>"0","0"&A1,A1)

Is there just one missing leading zero? If Dave's given you what you need then cool, otherwise let us know what values are entered and what values you want to convert them to.

Thanks

Welcome BTW!

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
19th January 2008
Posts
3

Hi Dave,

Thanks for the reply. It appears to add 0's to the front of the site code so:
AA1111
becomes
0AA1111
What I was looking for was a way to check the number of numeric characters and if it is less then 4 add 0s after the Alpha characters until the number of numeric characters was equal to 4.

so
A123
AA11
becomes
A0123
AA0011

Sorry if this wasn't clear.

Thanks,

Pete
Last edited by Rangerst; August 7th, 2008 at 21:21.

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
11th December 2007
Posts
36

Hi,

See attached. This is a molestation of the code from here:
http://office.microsoft.com/en-us/ex...CL100570551033

It uses an array formula (in column B) to return the position of the first number in the string being looked at. The rest is pretty basic and could easily be concatenated into one column (but broken out to show construction more clearly).

What do you think?

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

Hi,

In C1,

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1

In B1

=LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),"0000")

HTH

Kris, that's really smart!

I was trying to solve something a bit different (and now, obviously, wrong!). In any case, FWIW I added this to yours in case the final string should always be 6 characters long:

=LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),REPT("0",6-LEN(A1)+(LEN(A1)-C1)))

Excel Video Tutorials / Excel Dashboards Reports

9. Member
Join Date
11th December 2007
Posts
36

Oh that is a better way of finding the first number, but (for the sake of my own ego) the concatenation is virtually identical...

Would it be possible to define "numbers" as an array {0,1...9} in the same way you can define constants in a workbook? This could be used in many different applications.

Excel Video Tutorials / Excel Dashboards Reports

Originally Posted by great_big_bear
Would it be possible to define "numbers" as an array {0,1...9} in the same way you can define constants in a workbook? This could be used in many different applications.
Just tried it. Yes!

No, hang on - it's giving a different result.

Tried again - it works but you now have to enter the formula referring to the "numbers" array as an array formula.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno