Insertion of a space in a text string at a particular positi

TOFW

New Member
Joined
Sep 16, 2002
Messages
8
I have a large number of UK postcodes without any spacing. The correct format requires a space before the last 3 characters of the postcode. Postcodes vary in length from 5 to 7 characters. However I believe the format always has 3 characters at the end as a constant eg CV34 7RH, CV5 7RH, C1 7RH. I am able to seperate the charactors in a text string using the MID function and then remerge cells with a concatenate function. This works but is messy and is only able to work on a fixed length string. I need to change many hundreds in 1 column of varying lengths strings. Anybody have any ideas????
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Thanks the formula, it works on every variation. It wll save me hours of editing (I have circa 35,000 postcodes to clean)! many thanks for your help.
 
Upvote 0
Hi Im new to the forum and Im sort of having the same problem as the person above. Im using the dutch version.

Left=Links
right=Rechts
Len=Lengte

My current data are zipcodes as well. Some of them have the correct format and some dont.
The correct format is : 5021 CG

Some of the incorrect formats are:
5652sj
5652SJ
5652sJ
5652Sj
5652 sj
5652 SJ
5652 sJ
5652 Sj
5652 (Shouldnt be replaced, maybe its possible to add a color so that I can manaully check them myself)

I had a look at the above example but it didnt do me any good. I get a message that the code is wrong. Any one knows how to solve this?
Thanks in advance
 
Upvote 0
Hi,

Try:

=LEFT(A1,4)&" "&UPPER(RIGHT(A1,2))

wich I belive in Dutch would be:

=LINKS(A1;4)&" "&HOOFDLETTERS(RECHTS(A1;2))
 
Upvote 0
One last question,

What if i have a row of phone numbers that are missing a zero infront of the number, like this for example => 162683665 should be => 0162683665


Thanks
 
Upvote 0
HI
I have a formula like this
=SUMIF('2006_01_06'!$B:$B,$A6,'2006_01_06'!$C:$C)

I would like to change the date part thru a macro
by either using the value of a cell or thru func Today()


Thank You
 
Upvote 0
Hi,

I cannot get that date format directly from TEXT function (maybe because of my local settings) thus this lengthy suggestion:


=SUMIF(INDIRECT("'"&YEAR(TODAY())&"_"&TEXT(MONTH(TODAY()),"00")&"_"&TEXT(DAY(TODAY()),"00")&"'!B:B"),$A6,INDIRECT("'"&YEAR(TODAY())&"_"&TEXT(MONTH(TODAY()),"00")&"_"&TEXT(DAY(TODAY()),"00")&"'!C:C"))
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top