excel question

[lottodude]

hey guys,

my problem is, I have created excel file with like tons of numbers in it... it looks something like below (i know it looks stupid.. but i'm trying to created a table hoping it could help me when i pick #'s)

------------------------------------------------------------------------
[1][x][x][x][3][x][x][x][x][4][x][x][x][9][10][x][x][x][x][4]
[x][x][x][2][x][x][x][8][x][x][x][x][4][x][x][x][x][12][x][13]
[25][x][x][x][x][x][30][x][x][x][x][42][x][x][x][48][x][x][x]
....etc
--------------------------------------------------------------------------

what i'm trying to do is to make the the file look like following:

------------------------------------------------------------------------
[1][3][4][9][10][4]
[2][8][4][12][13]
[25][30][42][48]
....etc
--------------------------------------------------------------------------

I wanna delete like all the X's for each row, and shift the numbers to the left... I know i can do it row by row, highlight the x's section by section, and then right click mouse, then select delete cell...etc... but that's waaaaaaaaaay too many steps considering there are sooo many X's to be deleted.. (I have hundreds of rows..)..

so you guys know any quick way of doing it??

 

[GillesD]

Excel file modification

Based on what you wrote, I assume that each [x] or [#] are in different cells on a line and that the numbers you want are not always in the same position.

My solution would be: first replace all [x] with strings without text ( ""). Then create a string in the form "-01-03-04-09-10-04-" where all numbers are made of two digits. This can be obtained by concatenating all cells in a line using the TEXT function. Cells with string without text will not show any text. This should create a string of uniform format and length. Then you can extract each number into a specific cell using the MID function.

This may look complicated but, I think, it is easier to do than to explain.

Another solution would be to create a macro that would copy only number in adjacent colums.

 

[lottodude]

Re: Excel file modification

Based on what you wrote, I assume that each [x] or [#] are in different cells on a line and that the numbers you want are not always in the same position.

My solution would be: first replace all [x] with strings without text ( ""). Then create a string in the form "-01-03-04-09-10-04-" where all numbers are made of two digits. This can be obtained by concatenating all cells in a line using the TEXT function. Cells with string without text will not show any text. This should create a string of uniform format and length. Then you can extract each number into a specific cell using the MID function.

This may look complicated but, I think, it is easier to do than to explain.

Another solution would be to create a macro that would copy only number in adjacent colums.

Gilles D,
wow.. you seem to be very good with softwares.. such a quick and good reply..
thank you for the help, I'm gonna go give it a try..
appreciate your help
good luck to you on lotto