Moving post code data to a different column

[Guest]

Hi,

I have an excel sheet containing customer addresses. This sheet contains
following columns -

1. Customer Name
2. Address 1
3. Address 2
4. Address 3
5. Address 4
6. Address 5

Ideally I want all the postcode in 'Address 5' column.

Unfortunately since the address is not in any standard format, the post code
is every where. In some record, it is in second column. In some it is in 3rd
column and so on.

Is there any way I can move the post code into the 6th column (Address 5)?
The post code is all in caps.

Harish Mohanbabu

 

[Norman Jones]

Hi Harish,

Do all records include a post code?
Is the post code always the last field?
Do the post codes have a fixed format?

 

[Guest]

Harish,
If post code is always Upper Case and all other address
elements are mixed case then try :

Assumes data is colums A to F starting row 2

Lastrow=cells(rows.count,1).end(xlup).row
For r=2 to lastrow <===
For c=1 to 5
If cells(r,c).value=Ucase(Cells(r,c).value) then
Cells(r,6))=Cells(r,c).value
End if
next c
next r

HTH

 

[Guest]

Hi,

Thanks for your replies

Norman - to answer your queries -
1) yes - all records contain postcode
2) no - postcode is always not the last field. In some records, they are in
2nd column and in some they are in 3rd column and so on
3) they don't have fixed format. To give you some examples -
AB24 5QH, DY8 3HX, IG19JX etc

Toppers,
A daft question. I am not clear on the following -
- how to implement this solution
- also I don't understand certain what do you mean when you say 'Lastrow'.
Could you elaborate on this please.

Thanks in advance,

Harish Mohanbabu

 

[Norman Jones]

Hi Harish,

2) no - postcode is always not the last field. In some records, they are
in
2nd column and in some they are in 3rd column and so on

Rephrasing my question : is the post code always the final item? In other
words, if the post code appears in (say) column 2, subsequent columns will
be blank?

3) they don't have fixed format. To give you some examples -
AB24 5QH, DY8 3HX, IG19JX etc

If the post code is not necessarily two expressions, as in the last example,
and the position of the code is not fixed, how is the post code to be
identified? For VBA code to process the post code, it is necessary to
enunciate a means of unique identification.

 

[Guest]

Hi Norman,

Thanks for your reply and please see my response inline -

Rephrasing my question : is the post code always the final item? In other
words, if the post code appears in (say) column 2, subsequent columns will
be blank?

Yes - postcode is always the last column.

If the post code is not necessarily two expressions, as in the last example,
and the position of the code is not fixed, how is the post code to be
identified? For VBA code to process the post code, it is necessary to
enunciate a means of unique identification.

Postcode is always in caps. Rest of the address line is in title case.
Would it be possible at all to use this as unique identifier?

Cheers,

Harish Mohanbabu

 

[Norman Jones]

Hi Harish,

Thank you for the additional information.

One final question: is the post code always the only information in its
column, or can a post code and other adddress data appear in the same
column?

 

[Guest]

Hi Norman,

Postcode always appear alone in a separate column of its own.

Thanks

Harish Mohanbabu

 

[Norman Jones]

Hi Harish,

Try:

'=============>>
Public Sub TesterX()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range, rng1 As Range
Dim rcell As Range
Dim Lrow As Long
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Lrow = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = Range("G1:G" & Lrow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
' End With

For Each rcell In rng.Cells
Set rng1 = rcell.End(xlToLeft)
If rng1.Column <> 6 Then
rng1.Cut Destination:=rcell(1, 0)
End If
Next rcell

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

 

[Norman Jones]

Hi Harish,

An inadvertent apostrophe has appeared! Change:

.ScreenUpdating = False
' End With

to:
.ScreenUpdating = False
End With

---
Regards,
Norman

Hi Harish,

Try:

'=============>>
Public Sub TesterX()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range, rng1 As Range
Dim rcell As Range
Dim Lrow As Long
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Lrow = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = Range("G1:G" & Lrow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
' End With

For Each rcell In rng.Cells
Set rng1 = rcell.End(xlToLeft)
If rng1.Column <> 6 Then
rng1.Cut Destination:=rcell(1, 0)
End If
Next rcell

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

 

[Guest]

Thanks Norman It works great!

Cheers,

Harish Mohanbabu