Excel Help!

zerosoma · 12th May 2010, 6:30 PM

Hi,

Hope I'm posting this in the right place.

I have a column (B) that has a 13 digit number. What I need is a way to take the last 6 digits of each number in B (of which there are 1500) and put them beside each number in column C.

I also have another column, D which has 4.4l then 5.5l in each row, etc. Basically I need to remove all of the l's!

Can anyone think of a way to do this? It will save a heap of time

Thanks

MR CHILLED · 12th May 2010, 6:42 PM

Quote:

Originally Posted by zerosoma

I have a column (B) that has a 13 digit number. What I need is a way to take the last 6 digits of each number in B (of which there are 1500) and put them beside each number in column C.

=RIGHT(cell, 6)

and

=CONCATENATE(cell1, cell2)

Quote:

Originally Posted by zerosoma

I also have another column, D which has 4.4l then 5.5l in each row, etc. Basically I need to remove all of the l's!

Just do a find and replace for "l" in that column.

Lespom · 12th May 2010, 7:41 PM

Quote:

Originally Posted by MR CHILLED

Just do a find and replace for "l" in that column.

An alternative would be to use the following formula:

=VALUE(LEFT(A1,LEN(A1)-1))

This will remove the "l" from the entry and convert it to a number so it can be used in another formula.

MR CHILLED · 12th May 2010, 7:53 PM

Or use split columns if the valued are a fixed length, although probably unlikely.

So many ways to do the same thing in Excel...heh

zerosoma · 12th May 2010, 7:59 PM

that's awesome guys, got me out the shit

thanks!!

12th May 2010, 6:30 PM	#1
zerosoma Member Join Date: May 2004 Location: Melbourne Posts: 64	Excel Help! Hi, Hope I'm posting this in the right place. I have a column (B) that has a 13 digit number. What I need is a way to take the last 6 digits of each number in B (of which there are 1500) and put them beside each number in column C. I also have another column, D which has 4.4l then 5.5l in each row, etc. Basically I need to remove all of the l's! Can anyone think of a way to do this? It will save a heap of time Thanks

12th May 2010, 7:53 PM	#4
MR CHILLED D'oh! Join Date: Jan 2002 Location: Keep it up! :D Posts: 89,945	Or use split columns if the valued are a fixed length, although probably unlikely. So many ways to do the same thing in Excel...heh __________________ Co2 is weightless apparently. Be careful what you vote for. Howard on the economy: ''When the Prime Minister and the Treasurer and others tell you that the Australian economy is doing better than most – they are right,'' Asked how cash payments of baby bonus and school bonus are any different, Opposition Leader tony abbott says 'well look, they just are.'

12th May 2010, 7:59 PM	#5
zerosoma Member Join Date: May 2004 Location: Melbourne Posts: 64	that's awesome guys, got me out the shit thanks!!