Hi I just want to know how I can do the following.
I am importing a txt file into excel, on my first column I have numbers
12
30
50
13
99
103
106
So I want to know how can it be done, that when I import this it will put 4 (0000) in front of the two numbers and 3(000) in front of the three numbers by itself
It should look like this
000012
000030
000103
000106
In A1 Cell
103
In B1 Cell
=IF(A1="","",REPT("0",6-LEN(TRIM(A1)))&A1)
Drag it down...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Sixthsense
Thank You, but I am importing 3 diffrent files (3 working sheets), and then put them in one new sheet.
Here are two files, one is just how I have inport them (test.xlsx), and then one (Tapes....) of how it looks after I have worked on them.
It is on the All that I need it to show the 000/0000, and I am tring not to add a extra column as I have to sent this to other people.
You can finish this task easily without complicating it with import process.
Just import all the data from all your files and use a helper column to apply the suggested formula and copy paste the formula result as values in the source. Delete the helper column.
OR
Just leave it and use custom formatting...
Custom fmt 000000.jpg
Sixthsense
Thank you, will do it, but I have to hide the extra column, if I dlete it it takes out all the info in one column, but it is working when I hide it.
As suggested by coolblue the formula can be simplified as
=TEXT(A1,REPT(0,6))
You missunderstand my post. Check the image attached there.Originally Posted by :) Sixthsense :)
There is no need to use any formulas, nor mess arround cutting and pasting if the objective is for the numbers to just look like this. It cN be done very simply with custom formatting as shown in the image.
No not at all
Formatting a cell will change the View of the Data but it will not do any change with the actual data.
After formatting check it in formula bar the actual text will remains same.
So it is better to convert it as text with the help of formula
As I said, if it only needs to "look" that way then there is no need to change the data.
Last edited by coolblue; 05-20-2014 at 04:31 AM.
Can you integrate this to do it..?
Please Login or Register to view this content.
Last edited by apo; 05-20-2014 at 04:27 AM. Reason: Changed it so the array values were from Column A only..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks