excel question - please reply me at akhilesh.gupta1971@gmail.com with solution.

[akhilesh1971]

My question is;

suppose there is a name with emp id of a person and I want to seperate both into seperate columns like..


vinod18393 vinod 18393

manoj2739 manoj 2739

 

[akhilesh1971]

Another question is that to fill up the data into a columns we use ctrl+d

but if I want to fill up data from bottom to upto cells A1 in one column what is the shortcut key.


ctrl+d to fill up downwards

what key should we use for upwards.

answer me at akhilesh.gupta1971@gmail.com

 

[srinidhi]

Hi The answer to your first ? is here,

If there is a space b/w the name & the number u can directly got to

Select the data,

Data Tad>Text to Columns>Delimited>Next>Ticket in the space check box, you will already get an idea in the Data Preview, Click Next & finish, the name & the ID will be listed in separate coulmn.


The challenge is if there is no space b/w the name & the ID, then follow these steps.


Apply this formula, please change the row & column Numbers, here it is E5


=LEFT(E5,MIN(FIND(0,SUBSTITUTE(E5,{1,2,3,4,5,6,7,8,9},0)&0))-1)&" "&SUBSTITUTE(E5,LEFT(E5,MIN(FIND(0,SUBSTITUTE(E5,{1,2,3,4,5,6,7,8,9},0)&0))-1),"")


This formula will separate the name & ID in the same cell

Copy this & do a special paste in the next cell

Follow the Text to Columns given by me at the starting of my reply.

YOU ARE DONE, Now the names & Ids are separated in different cells.

 

[Luke M]

For 2nd question:

There is no built-in 1 shortcut key. To do:

Select Range of cells, exclude bottom cell.

type "=" (sans quotes)

hit the down arrow, then Ctrl+Enter. Voila!

Copy, Paste values only (if needed)

 

[charlesalvear]

You could also create a macro using the steps provided by LukeM and assign it to an unassigned keyboard combo like CTRL-E, CTRL-J, CTRL-M, etc, and that will give you your keyboard shortcut