Unsure what formula to use

[Finding_Facts_NC]

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?

 

[ShaneDevenshire]

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.

 

[Roger Govier]

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 C>Paste Special Values

 

[Don Guillett]

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

 

[Finding_Facts_NC]

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).

 

[Finding_Facts_NC]

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!

 

[Gord Dibben]

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

 

[Finding_Facts_NC]

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!

 

[Don Guillett]

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm