• 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.

Text data

G

GN0001

Member
I have a list of MemberIDs:


some of them are like

000234

MMM2345

123458


I use isText(cell) to verify if all the values are text. I receive false on some of them.

I need to change all these data types to text because I need to export the data from excel to access.


Therefore I use =Text(cell,"0") to change everything to text.

But when I do so the leading zeros would be dropped.

If I copy and paste the list as values, then the leading zeros also are dropped.


How can I keep the leading zeros as before?


Your help is greatly appreciated.

GGGGG
 
Hi GGGGG,


I copied and paste your data and its working fine for me.. all are text..


PS: I know.. its not sufficient answer for your query.. but your all provided data (in last three post also) are Data with sheet based.. So requesting to upload sample file.. so that we can check actual datatype of your data..


Regards,

Deb
 
Dear


Why should not use CONCATENATE function to prefix ' in every cells, it will change the values into text values.
 
By the way..

[list type=decimal]
[*]First Set a column's formatting as TEXT.. then paste the data there..
[*]Select column.. Text to Filter .. Set Column Data Format as TEXT..
add a '(apostrophe) before Numeric Text.. It will convert the Numeric Text to TEXT
Use formula =A1&"" .. it will convert A1 to TEXT.


Regards,

Deb[/list type=decimal]
 
Team,


I have 2000 rows. I sent only the sample of data.

I don't want to add to an extra character to cells, because then VLookUp function won't work.

I can't post all the data, I wish I could, it is MemberID.


GGGGGNNNNN
 
Hi GGGG,


If your concern is not to bother Vlookup function then you can use text to columns as


Deb said.


1.Select column.. Text to Filter


2.Select delimited option and Next and again Next


3. Select Text format under column data format tab and click finish.


Thanka
 
What you guys say looks fantastic, however I have to try it and get back to you.


You say:

.Select column.. Text to Filter

Do you mean Text to Column? I can't find Text to Filter in Text to column dialog box.


I need to use the result for VLookUp. Can the result be used in VLookUp?


Please advise me and thank you for the help.

Guity
 
1-First Set a column's formatting as TEXT.. then paste the data there..

GGGGG:


The data is imported from a database into Excel. I don't have any control on it.


Select column.. Text to Filter .. Set Column Data Format as TEXT..


GGGGG:


I have tried this, but I think it didn't work and I will get back on this to you.


add a '(apostrophe) before Numeric Text.. It will convert the Numeric Text to TEXT

Use formula =A1&"" .. it will convert A1 to TEXT.

GGGGG: My VLookUp won't work


Regards,
 
GGGGG,


I think i requested u somewhere on similar thread to upload a sample file... Plz provide sample workbook .....


Faseeh
 
Where should I download the file?

Also,

- I need to convert the data to text and then use code function to know what the char number for invisible characters in the text are and then use the result of code function into substitute function to replace the char with "" or no space to clean the column and ready for VLOOKUP. However so many things happen in this path. Let us start with the first issue.


Regards,

Guity
 
You must log in or register to reply here.
Back
Top