• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert from NUMBER format to TEXT format to accept another software

N

nazmul_muneer

Member
I have some data like...

Column A

8230053400

8230051100

8220022300

8220023510


I need to upload the values of this column A to my accounting software.


Condition-1 this data should be in TEXT format

Condition-2 file should be .dbf


In the excel sheet, I select the range and format it as TEXT. I Save this file as .dbf from .xls

When I upload this dbf file to my accounting software, it does not accept for number format


How can I convert this number value to text value?
 
Hi, namul_muneer!


No, you can't convert in the same column, you'll have to follow oldchippy's suggestion: copy from A to B pasting format/value, then you can copy from B to A pasting values only and after that clear/delete column B.


Regards!
 
@nazmul_muneer


You can convert from NUMBER to TEXT in the same column if you want to. It is a great way of removing numbers that come in to a spreadsheet in scientific notation as well, to avoid such lovely entries as 1E+06 etc.


Highlight the column [will also work on part of a column if you don't want to change other data above or below], and use Text To Columns - choose Delimited at step 1, remove ticks against ALL delimiters at step 2, then choose TEXT as the Column data format at step 3.


In Excel 2003 (we're a bit behind the times here) Text To Columns is in the Data menu. From memory I think you'll find it in the Data ribbon in 2007 onwards.


Hey presto, the numbers are now text.


MickM


PS My first post after months of feeding off other people's knowledge. Time I gave a little back. Great site


PPS This can also be achieved using VBA as well, of course.
 
Dear MickM,

At first, welcome to chandoo's forum

thanks for your response. Your solution is actually long process. I want to convert in short way
 
You could use a simple bit of VBA like:

[pre]
Code: 
Sub No_to_Text()
Dim c As Range
For Each c In Selection
c.Value = "'" + c.Text
Next
End Sub
[/pre]

Select the numbers

then run the VBA Code
 
Hui

Actually I want to upload data from dbf file to my Accounting software

The column "Account code" is text field. When I send this data, my MIS department complain me "It is number, please convert it to TEXT"
 
Hui,

Thank you very much. my problem has been solved through your solution.

I am very happy because I have got the answer from you.

Its working
 
Nazmul


As they say, don't knock it until you try it. My method sounds long winded, but only involves clicks of your left mouse button - typically 7 or 8 - and takes less than 10 seconds from start to finish. No typing of formulas, no finding macros in lists. On a column of 65536 records, Hui's code takes about a minute and a half! (Sorry, Hui, not knocking the code - it works perfectly - just making a point :) )


Hui


In my work I constantly encounter numbers that aren't really, that are often better formatted as text - telephone numbers generally (zero first digit) and when dialled internationally (often more than one zero), account numbers, credit card numbers and so on. They are numbers that you would seldom do any calculations on anyway, so the fact that are made up of digits is almost immaterial.
 
MickM


I understand the need to have numbers as text, I just wanted to make sure that Nazmul was absolutely sure he needed them as text


I find it more interesting that his MIS people can tell him what the problem is but couldn't assist him to solve it?
 
MickM

please don't misunderstand me. I observed your solution, its good and its correct. Actually I need to do this job several times in a month, so if I have a permanent solution like macro, it is better.
 
You must log in or register to reply here.
Back
Top