Find and Replace within String

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
Hello all, i have lot of strings in column A. Range is dynamic. The VBA has to find "A ***" within strings and replace with none. *** may be any value. Basically the word after the "A" has to be replaced along with A.
Example: if the string value is XXX DDD A D ZZZ then the output should be XXX DDD ZZZ.
After replacing with none, keep the first word of string in column A and move the rest of the data to column B. Taking previous output as example (XXX DDD ZZZ), XXX should be in column A and DDD ZZZ in column B
Thank you.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,637
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Assuming the "A" is always surrounded by a single space on each side and that your data starts in cell A1, give this macro a try...
VBA Code:
Sub RemoveAandNextWordThenSplit()
  Dim R As Long, Arr As Variant, Tmp As Variant
  Arr = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Preserve Arr(1 To UBound(Arr), 1 To 2)
  For R = 1 To UBound(Arr)
    Tmp = Split(Arr(R, 1), " A ")
    Tmp(1) = Mid(Tmp(1), InStr(Tmp(1), " ") + 1)
    Tmp = Split(Join(Tmp, " "), " ", 2)
    Arr(R, 1) = Tmp(0)
    Arr(R, 2) = Tmp(1)
  Next
  Range("A1").Resize(UBound(Arr), 2) = Arr
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,814
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Already solved!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,637
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Already solved!!
Unless your solution uses the same underlying logic (my guess is it doesn't), you should post your code so the OP and future readers of this thread can have a variety of solutions to pick from.
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Assuming the "A" is always surrounded by a single space on each side and that your data starts in cell A1, give this macro a try...
VBA Code:
Sub RemoveAandNextWordThenSplit()
  Dim R As Long, Arr As Variant, Tmp As Variant
  Arr = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Preserve Arr(1 To UBound(Arr), 1 To 2)
  For R = 1 To UBound(Arr)
    Tmp = Split(Arr(R, 1), " A ")
    Tmp(1) = Mid(Tmp(1), InStr(Tmp(1), " ") + 1)
    Tmp = Split(Join(Tmp, " "), " ", 2)
    Arr(R, 1) = Tmp(0)
    Arr(R, 2) = Tmp(1)
  Next
  Range("A1").Resize(UBound(Arr), 2) = Arr
End Sub
Hello Rick, i am getting error Subscript out of range in the line "Tmp(1) = Mid(Tmp(1), InStr(Tmp(1), " ") + 1)"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,637
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Then I think one of two things maybe be possible... either you have some "spaces" that are not real spaces (perhaps they are non-breaking spaces which usually come from copying data off the web) or you have multiple spaces where your example is showing single spaces. Since we cannot tell from what you have posted so far, I would suggest you upload your workbook with the original data (before running my code against it) to DropBox, mark it for sharing and post the link they give you for your file here so we can look at your exact data. Once we can see that we can modify the code to account for what the problem is.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,274
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option to try

VBA Code:
Sub Split_Data()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(.+)(\sA\s\S+\s)(.*)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "$1" & vbTab & "$3")
    Next i
    .Value = a
    .TextToColumns , xlDelimited, , , True, False, False, False, False
  End With
End Sub
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
Another option to try

VBA Code:
Sub Split_Data()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(.+)(\sA\s\S+\s)(.*)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "$1" & vbTab & "$3")
    Next i
    .Value = a
    .TextToColumns , xlDelimited, , , True, False, False, False, False
  End With
End Sub
Hello Pete, it is working as expected. Could you please split into 2 different VBA's, one for find and replace and another one to move the data of entire column A to B column except first word of column A. Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,274
Office Version
  1. 365
Platform
  1. Windows
it is working as expected.
Good news. (y)

Could you please split into 2 different VBA's, one for find and replace and another one to move the data of entire column A to B column except first word of column A.
That is not clear to me. Can you provide some sample data (say 8-10 rows) and the expected results with XL2BB and explain again in relation to those samples?
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
Good news. (y)


That is not clear to me. Can you provide some sample data (say 8-10 rows) and the expected results with XL2BB and explain again in relation to those samples?
Peter, initially 2 actions was combined in one VBA. That is Find and Replacing "A *" and moving the data(except first word) from A column to B column. So here i want to have separate VBAs for both actions. In between those 2 actions i have another one to include. So want you to split into 2 different VBAs. Example shown below. Thanks for your efforts.

Book2.xlsm
ABCD
1First VBA
2Input (Column A)Output (Column A)
3XXX DDD A D ZZZXXX DDD ZZZ
4XXX DDD D ZZZXXX DDD D ZZZ
5XXX DDD D ZZZXXX DDD D ZZZ
6XXX DDD A D ZZZXXX DDD ZZZ
7XXX DDD D ZZZXXX DDD D ZZZ
8XXX DDD D ZZZXXX DDD D ZZZ
9XXX DDD D ZZZXXX DDD D ZZZ
10XXX DDDD ZZZXXX DDDD ZZZ
11XXX DDD A D ZZZXXX DDD ZZZ
12XXX DDD D ZZZXXX DDD D ZZZ
13XXX DDD D ZZZXXX DDD D ZZZ
14XXX DDD D ZZZXXX DDD D ZZZ
15XXX DDD A D ZZZXXX DDD ZZZ
16XXX DDD A D ZZZXXX DDD ZZZ
17
18
19
20Second VBA
21Input (Column A) (Output of 1st VBAOutput (Column A)Output (Column B)
22XXX DDD ZZZXXXDDD ZZZ
23XXX DDD D ZZZXXXDDD D ZZZ
24XXX DDD D ZZZXXXDDD D ZZZ
25XXX DDD ZZZXXXDDD ZZZ
26XXX DDD D ZZZXXXDDD D ZZZ
27XXX DDD D ZZZXXXDDD D ZZZ
28XXX DDD D ZZZXXXDDD D ZZZ
29XXX DDDD ZZZXXXDDDD ZZZ
30XXX DDD ZZZXXXDDD ZZZ
31XXX DDD D ZZZXXXDDD D ZZZ
32XXX DDD D ZZZXXXDDD D ZZZ
33XXX DDD D ZZZXXXDDD D ZZZ
34XXX DDD ZZZXXXDDD ZZZ
35XXX DDD ZZZXXXDDD ZZZ
Sheet1 (3)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

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
Top