Conditioally insert characters to beginning of cell

D

dcliffo

Board Regular
Joined
Jun 18, 2003
Messages
148
I have a column of Supplier IDs. Maximum number of characters is 9. If the supplier ID is not 9 characters long, it should have 0's placed in the front so that there are 9 characters. For example, Supplier ID is 487695. It should look like 000487695. I can get Excel to format the cells correctly by using the custom format. However, when I import into Access, it trims the 0's away even though the field is identified as text because the custom format does not actually change the "value" of the cell. It merely formats the cells.

How can I get this column to correctly import into Access from Excel? :cry:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
In a new column, use =TEXT(A1,rept("0",9)) and copy down. Use this new column for your import.
 
Upvote 0
Thanks so very much! I knew it was simple, but I would have spent hours trying to figure that out. Never used rept before! :p
 
Upvote 0
If you suffer from a mild case of Redundantbracketphobia, you can also try:

=TEXT(A1,"000000000")


:LOL:
 
Upvote 0
Conditionally insert characters to beginning of cell

What if i have a case like i need to extend my TEXT data to a particular length :

E.g. I have entries like -

AAA
BBBB
CCCCC

And i want -
AAA@@
BBBB@
CCCCC

Such that there are same number of characters in each cell in a column.
Please provide a solution ?
 
Upvote 0
PA HS Teacher said:
How about:
=A1&REPT("@",MAX(5-LEN(A1),0))
Click to expand...

You probably want to refrase the formula cause when I tried it out, it works perfectly with the only exception that it completed it at the END of the cell and not the beginning as the op was requesting.
 
Upvote 0
mikerickson said:
How about =LEFT(A1&"@@@@@@@@@",9)
Click to expand...

Hi mikerickson.

Unfortunately that too completed towards the end and not the beginning. I guess when its a variable, its not as simple as it sounds to actually have it complete from the beginning.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Delete "." at the beginning and end of a string
Replies
2
Views
359
Chris101
C
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
612
diversification
D
M
  • Solved
How to sum a row of data with some cells containing an extra character
2
Replies
10
Views
700
Fluff
Fluff
S
  • Question
Too many characters in the cells of my Pivot Table?
Replies
0
Views
428
SorenN
S
A
  • Question
Datavalidation of social security numbers
Replies
2
Views
479
ausswe
A

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top