Switching characters around in VBA

[henry1972]

I need to switch characters around (namely £ so it sits before the numbers which proceeds it, and taking account of any decimal place). So if I have a instance of 100£ or 75.50£ to use VBA code to switch it to £100 and £75.50.

The numbers are just examples but hopefully it illustrates it could be any price. Not all prices will have this problem, some will be correct to start with. I need this to work for the whole of column B. Could anyone help? I'm new to VBA and I'm not sure where to start with this one.

 

[mole999]

delete the £ and format the cell as currency

 

[MARK858]

I don't see why you need vba for this (especially as you aren't familiar with it. So a formula method.
Click column C insert a new column, put the formula below in C2 and drag down, Copy/Paste Column C as values, delete column B.

=IF(RIGHT(B2,1)="£",RIGHT(B2,1)&LEFT(B2, LEN(B2)-1),B2)

or

Click column C insert a new column, put the formula below in C2 and drag down, Copy/Paste Column C as values, delete column B and as mole999 has suggested format as currency if £ is our regional setting.

=IF(RIGHT(B2,1)="£",LEFT(B2, LEN(B2)-1),B2)+0

 

[henry1972]

Thank you mole999 and MARK858 for getting back to me so quickly. I should also add the column in question contains strings which may or may not contain the pricing mentioned in my original question. My apologies, I should have mentioned that. I tried the formula and it didn't change the order but I'm guessing it's because I failed to mention this piece of information. I have used VBA code for some other stuff that I've done but it's very basic so if I have the code (or can be pointed along the right lines) I know where to add it.

 

[MARK858]

If your cells are constants not formulas and your regional settings are UK i.e. £ symbol then try

With Columns("B:B")
        .Replace What:="£", Replacement:="", LookAt:=xlPart
        .Columns("B:B").NumberFormat = "£#,##0.00"
End With

I should also add the column in question contains strings which may or may not contain the pricing mentioned in my original question

The formulas I posted already allowed for that. Did you test?

 

[Peter_SSs]

Could we have 5-10 rows of varied sample data and the expected results?

 

[henry1972]

Could we have 5-10 rows of varied sample data and the expected results?

Absolutely Peter. Please see below. Originally I was just going to have £ but I've added in $ as well so there could be different currencies but those will be the main two. As you can see it's the 3rd and 5th rows that need to change the others are all OK. Does this explain what I'm looking to achieve? If it needs clarification, just let me know. Thanks in advance. I really appreciate everyone's help.

[TABLE="width: 729"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Title[/TD]
[TD]Expected result[/TD]
[/TR]
[TR]
[TD]EXTRA 15% off selected plans[/TD]
[TD]EXTRA 15% off selected plans[/TD]
[/TR]
[TR]
[TD]Extra 20% off sales[/TD]
[TD]Extra 20% off sales[/TD]
[/TR]
[TR]
[TD]15£ off orders over 150£[/TD]
[TD]£15 off orders over £150[/TD]
[/TR]
[TR]
[TD]£5 off first order for ALL NEW CUSTOMERS[/TD]
[TD]£5 off first order for ALL NEW CUSTOMERS[/TD]
[/TR]
[TR]
[TD]Get 15$ off 100$ spend on your next order[/TD]
[TD]Get $15 off $100 spend on your next order[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Hmm, that is a bit different to what you asked for in post # 1.

 

[Peter_SSs]

Try this on a copy of your data.

Sub Fix_Pounds()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\b\d*\.?\d*)(£|\$)(?= |$)"
  a = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = RX.Replace(a(i, 1), "$2$1")
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub