Removing characters with VBA

[paulsolar]

Hi All

I thought this was going to be easy to accomplish but I'm having a lot of trouble with it.

I have a long list of postcodes, in the UK postcodes come in quite a few formats but i just want to achieve a simple thing.

Postcodes can look like the below:

NP12 9AF
NP126AF
CF32 9PP
B1 9AA

I only need the letters before the first number, so my new list would look like the below

NP
NP
CF
B

I'm sure there must be an easy way of doing this but cant seem to come up with a simple solution

Any help is greatly appreciated.

Cheers

Paul

 

[welshgasman]

Inspect each character in a loop, when a numeric is found stop.
https://social.msdn.microsoft.com/F...termine-if-a-character-is-number?forum=isvvba

 

[paulsolar]

Hi Gas Man

many thaks for your reply

sorry to say im still struggling with this, I'll keep you posted as to how i get on

cheers

Paul

 

[Fluff]

How about

Sub Postcodes2()

   Dim Ary As Variant
   Dim Rw As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp))
   
   For Rw = LBound(Ary, 1) To UBound(Ary, 1)
      If IsNumeric(Mid(Ary(Rw, 1), 2, 1)) Then
         Ary(Rw, 1) = Left(Ary(Rw, 1), 1)
      Else
         Ary(Rw, 1) = Left(Ary(Rw, 1), 2)
      End If
   Next Rw
   Range("B2").Resize(UBound(Ary, 1)) = Ary

End Sub

This assumes the postcodes are in Col A & will put the result in col B

 

[Eric W]

If you just want a formula:

Excel 2012

	A	B
NP12 9AF	NP
NP126AF	NP
CF32 9PP	CF
B1 9AA	B

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=LEFT(A1,MIN(IF(ISNUMBER(MID(A1&1,{1,2,3,4,5,6,7,8,9},1)+0),{0,1,2,3,4,5,6,7,8})))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[paulsolar]

Hi Fluff

twice in two days you've saved my life, absolutely fantastic.

I can go to work tomorrow without having to panic
Cheers

Paul

 

[Fluff]

Glad we could help & thanks for the feedback

 

[kvsrinivasamurthy]

Fomula Answere
A3=Data
ARRAY Formula in B3

=IFERROR(LEFT(A3,SMALL(IF(ISNUMBER(FIND(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1),"123456789")),ROW(INDIRECT("1:"&LEN(A3))),""),1)-1),"")

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[paulsolar]

Hi Eric

I was going down the formula route until Fluff came up with the superb bit of code.

Albeit my formula was about ten times longer than yours

thanks for your help Guys really appreciate it.

cheers

Paul

 

[Rick Rothstein]

How about

Sub Postcodes2()

   Dim Ary As Variant
   Dim Rw As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp))
   
   For Rw = LBound(Ary, 1) To UBound(Ary, 1)
      If IsNumeric(Mid(Ary(Rw, 1), 2, 1)) Then
         Ary(Rw, 1) = Left(Ary(Rw, 1), 1)
      Else
         Ary(Rw, 1) = Left(Ary(Rw, 1), 2)
      End If
   Next Rw
   Range("B2").Resize(UBound(Ary, 1)) = Ary

End Sub

This assumes the postcodes are in Col A & will put the result in col B

You have so many lines of code.

[table="width: 500"]
[tr]
	[td]Sub PostCodes()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(A1:A#="""","""",LEFT(A1:A#,2-(ISNUMBER(-MID(A1:A#,2,1)))))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]