keep leading zeros when I add quotation mark in excel - excel

I have a list of SSN in my excel file (9 digits, some of which have leading zeros, no hyphen).
I keep them in the special format (numero da seguranca social in portugues) so I can keep leading zeros in the column.
I want to add ' ', to the column so that I can search them in SQL query in bulk.
When I use concatenating formula ( concatenating("'",B2,"'",",")), the leading zeros are gone.
How can I achieve the result of 00XXXXXX as '00XXXXXX', ?
Thank you!

I am not sure what your special format is, but I am guessing that if the value is xx the format makes it look like 00xx. Similar to a zip code.
So the value when you change it to text looks like xx.
I can think of 2 ways to fix it
Write a formulae which basically does what the special format does. For example it would make xx as 00xx. Then you can concatenate this new value like u mentioned earlier
Copy the values from excel to notepad and then paste it to excel again. When you copy from excel to notepad, the formatted value, i.e. 00xx will get copied to notepad which can then be pasted to excel in a separate unformatted cell.

One way of doing this is to copy your SSN column from your file and paste it into a new file all by itself. Then, while ensuring that the SSN numbers are formatted with any appropriate leading zeros, save the new file as a text/tab delimited file and close it. Now, using Excel's Open File command, select your text file and on Step 3 of 3, select Text for the Column data format. Now, you should be able to select that column of data and copy it back to your main file and perform your concatena

Did you try ''00XXXXXX' or "'00XXXXXX"? That is, add one single quote before the number and Excel interprets it as a string, not a number. If you will not do any numeric operations with it, then it should be a string.

Thank you all for your input.
I ended up using #tigeravatar 's method, which is
="'"&TEXT(B2,"000000000")&"'"&","
and it worked for me.
Thank you!

Related

Excel, OleDb, and Leading zeros

I have a process that using OleDb reads data from an excel file into a DataSet. Everything was working well until I started to see data with leading 0's in it. The text is formatted as General or Text.
I have set IMEX=1 in the OleDb connection, yet I still get a null value in the dataset for each entry with a leading 0.
Does anyone know a way around this that WORKS?
Edit
As a bit of added information, I MUST keep the leading 0's and the column is formatted as General. I have also tried Text.
The first entry that has a leading zero is in row 11 and a null is returned. All other data on this specific sheet is numbers for this column.
I bet the data is stored in the Excel spreadsheet as text; because it wouldn't natively have leading zeroes (although the cells could be formatted to show them.) I expect OleDb is asking Excel what datatype, and Excel thinks they are strings.
Look at one of the Excel cells nad see if it has a loading zero in the data input strip at the top; or does it have a leading apostrophe or some such?
If ihis is the case (or even if it's not, but you want unformatted data), one way off the top of my head would be to copy the worksheet to another worksheet and transform the Excel column with the Val(address) function, and reformat it without leading zeroes; then read from that.
The best thing that I can see is that, you have to create a preformatted excel file, then use it for the data that you are requiring.
Are your first 10 rows pure numeric data?
Then the driver is making a judgment call that the whole column is numeric. It chokes when it runs into data that doesn't fit. You are probably seeing this as NULL or 0.
I would try:
1) drop the IMEX - experiment without it
2) Try adding a single quote as the first character to all the data in that column.
This "forces text" in Excel. As a test just try it on the first 11 rows.
This should force the driver to see the column as text. The NULLs should disappear.
There is some way to increase the row "sample size"... but I can't remember the keyword to use in the connection string.

In Excel Strings that look like numbers in a column need to be interpreted as just a string

I have a column in my Excel spreadsheet that has text that look like:
413,781,222
That looks like a number to excel. I want it to be interpreted as one big string.
How do I do that?
Many Thanks!
The problem is as soon as excel recognizes it as a number it removes the formatting from the cell value and uses formats to add commas and such.
To maintain the commas you either need to enter the values with a ' in front or have the cell formatted as a text cell before pasting/typing the value in.
You could also use the function =TEXT(F4,"#,##0") to store the values as strings with commas and then copy paste over.
I often do this:
=A1&""
...or:
=R1C1&""
That'll make a number a string.
Stick an apostrophe ' in front of the value...
I am assuming you have this text in Cell A1.
Try using this:
=Text(A1,"###,###,###")
I did this to your data, then copied and pasted values only, and the value is '413,781,222' commas included. You can copy/paste your data and let Excel change it to a number, but then...change it back to the format you like with , in the values.
Putting an apostraphe before each cell is so manual! It works great for limited number of items, but I have learned to hate doing this (obviously spent too much time doing this in the past)
This works for 781669 as well as your original example

Number value as text

I have a single cell with the value:
426,427,433,439,442
This isn't a number, rather a list of numbers. If I try to add another number to the list, for example, 679. Excel changes the cell to read:
679,426,427,433,439,000
If I select the cell and format it as "text", it changes to:
4.26427E+14
I've tried various cell formatting options, but I can't seem to get Excel to treat these numbers like text.
Copy and paste the column into Notepad, format a new column in Excel as text. In Notepad select all, copy it back out from Notepad into the column in Excel that you formatted for text.
First place a single quote (apostrophe) in front of the set of numbers and add the latest value at the end appropriate position.
add (apostrophe) in front of numbers
add (comma's) between
add any number by adding a (comma)
Possibly:
=LEFT(A1,3)&","&MID(A1,4,3)&","&MID(A1,7,3)&","&MID(A1,10,3)&","&MID(A1,13,3)
It seems you have a number 426427433439442 with the commas purely a presentational aspect of the formatting. I take it you want the commas and the only way now may be to insert them.

Need to strip off leading zeros in excel

I downloaded banking account data in a csv file with fields separated by commas. I converted it into excel file succesfully, except by the fact that I couldn't remove the leading zeros en the numeric financial fields. Looks like they were pasted as text. I need to strip off the leading zeros from excel cells, and handling the numeric data afterwords. Any help?
By the way: I found a lot of instructions in the web -including this site- to help doing exactly the opposite, I mean to prevent stripping off leading zeros. That is not what I need. I need the data without leading zeros.
Thanks for your help.
Simple fix for numbers stored as text: enter the number 1 in a numeric cell (any will do, this is a throwaway). Copy the cell that contains 1. Select all the text numbers you want to convert to numeric. Then do Edit -> Paste Special -> Multiply. This will multiply all the data in those cells by 1. Excel will have to convert the text to numbers to do this, and the result will be a simple conversion (since anything times 1 is itself).
Let us know how it goes.
Try using the Value() function. It converts numeric strings, even with commas.

Comma Missing When Converting From xls to csv

I have an excel spreadsheet that has two columns. When I choose to save it as a csv file, the comma after the second column is not in place. For example, i get this:
Invoice,SID No.
156106,ELC204R8
156106,WXC2048V
Instead of this:
Invoice,SID No.,
156106,ELC204R8,
156106,WXC2048V,
How to I get the comma in right before the line break?
This as I assume you are aware is not a usual requirement/format for saved CSV file.
To achieve what you are trying to do though, you need to trick Excel into thinking you are exporting a third blank/empty column.
One way to do this is to add the single-quote character ' into a third Excel column before exporting.
I just entered a space into the first 20 rows and that solved the issue.

Resources