I have been send a spreadsheet that is set up like the example below

A
1 60112,"1"
2
3 60113,"1"
4
5 60114,"1"
6
7 60121,"1"
i need to remove the blank cells(e,.g A2,A4 etc) and get rid of the
punctuation then in Cell B2 i need the otther number so it would look like
A B
1 60112 1

the spreadsheet has about a thousand rows can this be done or will it have
to be done manually which would take a considerable amount of time as i have
about 20 of these to do a day

I posted this question yesterday but it appears to have dissapeared so if
this is a duplicate apologies


Thanx for any help

DatText to ColumnsDelimited by Comma and Finish.

Does it for me with your example data.


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 14:56:01 -0800, cufc1210
wrote:

I have been send a spreadsheet that is set up like the example below

A
1 60112,"1"
2
3 60113,"1"
4
5 60114,"1"
6
7 60121,"1"
i need to remove the blank cells(e,.g A2,A4 etc) and get rid of the
punctuation then in Cell B2 i need the otther number so it would look like
A B
1 60112 1

the spreadsheet has about a thousand rows can this be done or will it have
to be done manually which would take a considerable amount of time as i have
about 20 of these to do a day

I posted this question yesterday but it appears to have dissapeared so if
this is a duplicate apologies


Thanx for any help

hi
are all of the data layed out like your example? is there any other data
next to the data in question? do you know how to use macros?
you can get rid of the blank row simply by sorting the data. blank rows will
sort to the bottom.
if all of your data is layed out like your examples then this macro might do
it for you.
Sub cufc1210()
Dim r As Range
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A2:A" & lr)
For Each cell In r
cell.Offset(0, 1).Value = Mid(cell, 8, 1)
cell.Value = Left(cell, 5)
Next cell
End Sub

post back if problems

Regards
FSt1

"cufc1210" wrote:

I have been send a spreadsheet that is set up like the example below

A
1 60112,"1"
2
3 60113,"1"
4
5 60114,"1"
6
7 60121,"1"
i need to remove the blank cells(e,.g A2,A4 etc) and get rid of the
punctuation then in Cell B2 i need the otther number so it would look like
A B
1 60112 1

the spreadsheet has about a thousand rows can this be done or will it have
to be done manually which would take a considerable amount of time as i have
about 20 of these to do a day

I posted this question yesterday but it appears to have dissapeared so if
this is a duplicate apologies


Thanx for any help

Thanx a lot guys both of them worked fantastic and will save me hours of work
my assistant will be most grateful as well

Cheers again guys

"Gord Dibben" wrote:

DatText to ColumnsDelimited by Comma and Finish.

Does it for me with your example data.


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 14:56:01 -0800, cufc1210
wrote:

I have been send a spreadsheet that is set up like the example below

A
1 60112,"1"
2
3 60113,"1"
4
5 60114,"1"
6
7 60121,"1"
i need to remove the blank cells(e,.g A2,A4 etc) and get rid of the
punctuation then in Cell B2 i need the otther number so it would look like
A B
1 60112 1

the spreadsheet has about a thousand rows can this be done or will it have
to be done manually which would take a considerable amount of time as i have
about 20 of these to do a day

I posted this question yesterday but it appears to have dissapeared so if
this is a duplicate apologies


Thanx for any help



.