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
--
MBS Axapta - MVP
http://www.harishm.com/

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?


---
Regards,
Norman



"Harish Mohanbabu" wrote in message
...
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
--
MBS Axapta - MVP
http://www.harishm.com/

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
"Harish Mohanbabu" wrote:

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
--
MBS Axapta - MVP
http://www.harishm.com/

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
--
MBS Axapta - MVP
http://www.harishm.com/

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.

---
Regards,
Norman


"Harish Mohanbabu" wrote in message
...
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
--
MBS Axapta - MVP
http://www.harishm.com/

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
--
MBS Axapta - MVP
http://www.harishm.com/

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?


---
Regards,
Norman


"Harish Mohanbabu" wrote in message
...
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
--
MBS Axapta - MVP
http://www.harishm.com/

Hi Norman,

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

Thanks :)

Harish Mohanbabu
--
MBS Axapta - MVP
http://www.harishm.com/

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
'<<=============


---
Regards,
Norman



"Harish Mohanbabu" wrote in message
...
Hi Norman,

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

Thanks :)

Harish Mohanbabu
--
MBS Axapta - MVP
http://www.harishm.com/

Hi Harish,

An inadvertent apostrophe has appeared! Change:

.ScreenUpdating = False
' End With

to:
.ScreenUpdating = False
End With

---
Regards,
Norman



"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman



"Harish Mohanbabu" wrote in message
...
Hi Norman,

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

Thanks :)

Harish Mohanbabu
--
MBS Axapta - MVP
http://www.harishm.com/

Thanks Norman :) It works great!

Cheers,

Harish Mohanbabu
--
MBS Axapta - MVP
http://www.harishm.com/