Moving post code data to a different column

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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/