|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 23rd, 2008, 06:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Unsure what formula to use
I have a column containing three digit numbers and four digit numbers. I
need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this? 
|
|
#2
January 23rd, 2008, 06:56 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
Hi,
You need to use VLOOKUP(A1,Table,2,false) Where Table is a range the first column containing the list of company names, the second containing the 4 digit number you want. -- Cheers, Shane Devenshire "Finding_Facts_NC" wrote: I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#3
January 23rd, 2008, 06:58 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
Hi
Assuming column C is empty at present, enter in C1 =IF(LEN(B1)=3,C2,B1) and copy down to C6000 To fix the values, copy column CPaste Special Values -- Regards Roger Govier "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#4
January 23rd, 2008, 07:25 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
try this
Sub fixnums() mycol = 4 For i = Cells(Rows.count, mycol).End(xlUp).Row To 2 Step -1 If Len(Cells(i, mycol)) = 4 Then Cells(i, mycol + 1).Value = Cells(i, mycol) Else Cells(i, mycol + 1).Value = Cells(i + 1, mycol + 1) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#5
January 23rd, 2008, 07:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
I've tried both formulas and they are not returning the value I need. I need
to replace the three digit number with the first four digit number listed below the three digit number(s). "Roger Govier" wrote: Hi Assuming column C is empty at present, enter in C1 =IF(LEN(B1)=3,C2,B1) and copy down to C6000 To fix the values, copy column CPaste Special Values -- Regards Roger Govier "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#6
January 23rd, 2008, 08:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
Don, I'm not familiar with writing macros and uncertain how to proceed with
the code? It may very well work to resolve my problem, but I will need to get more guidance on how to use it first. Thansk for the response! "Don Guillett" wrote: try this Sub fixnums() mycol = 4 For i = Cells(Rows.count, mycol).End(xlUp).Row To 2 Step -1 If Len(Cells(i, mycol)) = 4 Then Cells(i, mycol + 1).Value = Cells(i, mycol) Else Cells(i, mycol + 1).Value = Cells(i + 1, mycol + 1) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#7
January 23rd, 2008, 08:24 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
Roger's formula works for me using the data you supplied and his instructions
about placement in C1 and copying down. Gord Dibben MS Excel MVP On Wed, 23 Jan 2008 11:28:09 -0800, Finding_Facts_NC wrote: I've tried both formulas and they are not returning the value I need. I need to replace the three digit number with the first four digit number listed below the three digit number(s). "Roger Govier" wrote: Hi Assuming column C is empty at present, enter in C1 =IF(LEN(B1)=3,C2,B1) and copy down to C6000 To fix the values, copy column CPaste Special Values -- Regards Roger Govier "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
|
#8
January 23rd, 2008, 09:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unsure what formula to use
FYI... Here's the formula I got to work for me in Column C
=IF(LEN(B1)=3,IF(LEN(B2)=3,IF(LEN(B3)=4,B3),B2),B1 ) There may be a better looking formula, but wanted to post it in case it works for others. Thanks for your response! "Finding_Facts_NC" wrote: Don, I'm not familiar with writing macros and uncertain how to proceed with the code? It may very well work to resolve my problem, but I will need to get more guidance on how to use it first. Thansk for the response! "Don Guillett" wrote: try this Sub fixnums() mycol = 4 For i = Cells(Rows.count, mycol).End(xlUp).Row To 2 Step -1 If Len(Cells(i, mycol)) = 4 Then Cells(i, mycol + 1).Value = Cells(i, mycol) Else Cells(i, mycol + 1).Value = Cells(i + 1, mycol + 1) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Finding_Facts_NC" wrote in message ... I have a column containing three digit numbers and four digit numbers. I need to replace the three digit numbers with the first four digit number listed below them. Here's an example of the numbers listed in column "B" and what the result should look like in column "C" A B C Co Name 1 119 5530 Co Name 1 449 5530 Co Name 1 5530 5530 Co Name 2 119 5513 Co Name 2 5513 5513 Co Name 3 119 5540 Co Name 3 449 5540 Co Name 3 559 5540 Co Name 3 5540 5540 There are over 6,000 rows. Is there a formula that will help me do this?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
