Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel question - need assistance

Posted on 2011-10-11
43
Medium Priority
?
236 Views
Last Modified: 2012-05-12
hello, i'm trying to paste in a column of international phone numbers that begin with "+"
for example: +01199299992
when i copy from an .xls file and paste this into a .CSV, the format is changed to something like this:
3.90113E+11
can someone please provide a way for me to paste these into a .CSV file and preserve the number syntax?

thx in advance!
0
Comment
Question by:siber1
  • 17
  • 9
  • 6
  • +4
43 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36950136
siber1,

Put a single-quote ' before it. This won't display/print in Excel but will stop Excel from interpreting it as a number.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950190
siber1,

And if you want it back as a number, simply multiply it by 1.

Regards,
Brian.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950196
either preformat the spreadsheet column as 'text' - using 'cells' 'format'  and then paste. Depending on which version of excel you are using you may get a warning that you have stored a number as text

or use the text import wizard and make sure you tell it that column is text on the way in - this avoids the 'number as text' warning

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 26

Expert Comment

by:redmondb
ID: 36950202
siber1,

Alternatively,  format the cell as Text.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950213
Apologies, regmigrant, crossing posts.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950255
a privilege, sir
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950278
Nope, just a slower Brian!
0
 

Author Comment

by:siber1
ID: 36950390
hi guys, i tried both of those approaches:
format columns as Text, and import using the wizard, and i still have the same results.
still seeing this format:
3.90113E+11

0
 

Author Comment

by:siber1
ID: 36950429
also, i am using excel 2010

thx
0
 
LVL 1

Expert Comment

by:deyotter
ID: 36950435
It could be that all you need to do is make the column wider, or the font smaller in order to display the entire number.  

Try that and see if it works for you.

Good Luck,
Lorrie
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950458
siber1,

I'm also using 2010 and they both work fine. Exactly how are you doing the copy/paste, including to what and from what?

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950482
siber1,

Column width makes no difference for me. (I tried pasting cells to Notepad and also saving the file as CSV - both fine.)

Regards,
Brian.
0
 

Author Comment

by:siber1
ID: 36950512
hmm.. for example, the first column on an .xls file has this number:
+390112913430

when i paste that into a new .CSV [after formattign the columns as text]
it displays as this after i close and re-open the .CSV
3.90113E+11

0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950532
siber1,

Unless you "PasteSpecial Values", the destination cell will have its Type changed to the source cell.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950546
siber1,

What's the content and type of the source cell? Want to post it?

Thanks,
Brian.
0
 
LVL 1

Expert Comment

by:deyotter
ID: 36950577
You could try a custom format.  I used  +## (###) ###-#### and it displays as +39 (011) 291-3430

Lorrie
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950581
siber1,

My mistake, I missed "... and re-open". All my comments were about saving it with the "+".

I'm surprised regmigrant's suggestion didn't work - it certainly does for me. Please open the CSV file in NotePad and tell us what you see.

Regards,
Brian.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950584
When you say you are pasting it into a 'csv' file what exactly do you mean? which application is the CSV file in?
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36950609
I think the issue here is that the original XLS has formatting applied to a number to make it display as a phone number.  But when you copy that value to a new worksheet, and then save that as a CSV, all formatting is lost, so it just treats it as a number.

There is no way to store formatting in a CSV file, and I don't think there is a way to copy the formatted version of a cell to another workbook as an unformatted cell.

~bp
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36950629
Hmmm, I may stand corrected, I tried a test on XL2010 and it seems to save the "formatted version" of the source phone number into the CSV file, so it seems like it should be working...

~bp
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950643
I think BP has hit the very nail on the head - a custom format has been applied to the numbers and its being lost in the cut/paste

However - if you go to the Format cells dialog and get the custom format (or try the one below) you can use:-

=text(a1,"+############") - assuming a1 is the start of the list and "+####.." is the format - copy this down and you will have actual text versions of the numbers which should work as described in previous answers

0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950666
oh, use 0 instead of # to preserve leading zeroes
0
 
LVL 81

Expert Comment

by:byundt
ID: 36950682
siber1,
When you open a CSV file in Excel, it tries to guess whether each cell contains a number or text. It it biased towards assuming that the cell contains a number, hence the scientific notation when you reopen the file. If you open it in Notepad, however (and as redmondb suggested), you will find that the leading plus sign is still there.

The takeaway message is that Excel is an unreliable way of determining the content of a CSV file. Notepad is much more reliable as a CSV "inspector" because it makes no attempt to format or interpret.

The attached file was created in Excel 2010 and has two columns of international numbers. One column was prefaced with a single quote. The other column was preformatted as text prior to data entry. In both cases, Notepad shows the leading +, but Excel removes it.
PhoneNumber.csv
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 1000 total points
ID: 36950692
==> hmm.. for example, the first column on an .xls file has this number:
==> +390112913430

Do you have any formatting applied to that in the source excel?

In the CSV do you see 390112913430 in that column?

~bp
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950698
siber1,

There's a lot of theorising going on here! Could you put us out of our misery by posting the source spreadsheet (delete everything except that cell) and your output CSV file?

Thanks,
Brian,
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36950785
we were doing so well without the original author - typical techies :)
0
 

Author Comment

by:siber1
ID: 36950823
sure Brian, sorry for the confusion. i've attached a subset of the exact CSV that im working on.

you can see the format is: 3.90113E+11
i'm simply trying to get the format to stick as:
+390112913430 so that i can import this back into AD.

thx much
sample.csv
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36950839
I think we also need to see the original excel sheet, and see the exact steps you followed to create this CV file.

~bp
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950846
siber1,

Thanks. And the spreadsheet, please? (The problem is coming from there as the CSV shows us that the data was saved as numbers.)

Regards,
Brian.
0
 

Author Comment

by:siber1
ID: 36950881
sure, here is a subset of the source .XLS file
sourcefile.xls
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950906
Thanks, siber1.

Simply change the cell formats to Text and save the file as a csv. Text wizard can then happily open it as regmigrant mentioned a long way above!

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36950915
(Not forgetting to change the two columns to Text in Text Wizard.)
0
 

Author Comment

by:siber1
ID: 36950995
guys i dont know what to tell you, when i follow those steps, and run the text import wizard, i still see the same format:
3.90113E+11

maybe its just my version of excel? not sure why it is doing this, i've done this many time b4 and never had this problem.

0
 

Author Comment

by:siber1
ID: 36951081
can someone send me a version of the CSV that works for you, and ill try to open on my local client?

thx
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36951107
Here you go.

~bp Book1.csv
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 1000 total points
ID: 36951184
siber1,

In your xls, please select columns C & D and change their format from "General" to "Text". This is the source of your problem.

To check, just save the updated file as a CSV, and open that in NotePad.

Regards,
Brian.
0
 
LVL 81

Expert Comment

by:byundt
ID: 36951244
When I saved siber1's .xls file in .csv format and reopened in Notepad, there was no change to the phone numbers. There was no need to change the column format to Text before saving.

If you change the format of a column of data after having entered the data, no change is made to the pre-existing data. For it to have any benefit, you need to change the format to Text prior to entering the data.

Brad
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36951321
Thanks, Brad. That was new to me.

siber1, you're in capable hands and I'm not adding anything, so I'm bowing out. Sorry I couldn't help.

Regards,
Brian/
0
 
LVL 10

Expert Comment

by:broro183
ID: 36952880
hi everyone,

Here is a simple macro that I wrote recently for "trimming cell contents "in place" and have modified slightly to add a single appostrophe at the start of a cell's value. This may be of use for the files where Siber1 has existing data. Note, this doesn't have error handling & therefore it relies on users being aware that the code works on the currently selected cells.

Siber1, provided your column widths are wide enough (& no non-default font formatting is applied) you will get a visual warning of what data type Excel considers your information to be, because Text is left aligned & Numeric values are right aligned by default.

Sub TrimRng()
'10/07/2011, RB: written
Const ap_ch As String = "'"    '"ap_ch" = appostrophe character
Dim rngArr As Variant
Dim NumOfRows As Long
Dim NumOfCols As Long
Dim RowInd As Long
Dim ColInd As Long
    With Selection
        rngArr = .Cells
        NumOfRows = .Rows.Count
        NumOfCols = .Columns.Count
    End With
    For RowInd = 1 To NumOfRows
        For ColInd = 1 To NumOfCols
            '11/10/2011, RB: this should be fine due to the inclusion of "'" at the start of the string
            If Left$(rngArr(RowInd, ColInd), 1) <> ap_ch Then
                rngArr(RowInd, ColInd) = ap_ch & Trim$(rngArr(RowInd, ColInd))
            End If
        Next ColInd
    Next RowInd
    Selection = rngArr
    ''or to preserve the original data, the results can be put in another column eg...
    'Selection.Offset(0, 1) = rngArr
    MsgBox "done"
End Sub

Open in new window


hth
Rob
0
 

Author Comment

by:siber1
ID: 36955339
hi Bill, I just opened that .CSV that you posted and this is the format i see in excel:
3.90113E+11
112913421
112913422
3.90113E+11
3.90113E+11
112913426
112913427

i think it must be a setting on my excel that is causing the numbers to display like this. if its displaying fine in your version of excel. is there a setting that i can change to display the values properly?

thx
0
 
LVL 81

Expert Comment

by:byundt
ID: 36955357
siber1,
Do not open at a CSV file using Excel and draw any conclusions about its contents!  Excel is notorious for modifying the data when it opens a CSV file.

Use Notepad instead.

Brad

0
 

Author Closing Comment

by:siber1
ID: 36977268
thx for the assistance with this!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36977447
siber1,

Thanks - but I'm not sure I'm a worthy recipient of points. What did your problem turn out to be in the end?

Regards,
Brian.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Six Sigma Control Plans

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question