If, Iferror ,Search and concatenate formula required for below example

N

nikhil0311

Board Regular
Joined
May 3, 2013
Messages
200
Office Version
  1. 2013
Platform
  1. Windows
I have a excel file and value in Column A1 is "BAC (21-40%),WFC (21-40%),JPM (0-20%)" and value in

Column B1 is"AMSOUTH (N/A), BBVA (N/A),STI (N/A),BBT (N/A),WFC (N/A)"

i want to do text to column and concatenate the column A1 & B1 to get the final output in

Column C1 as "BAC, WFC, JPM, AMSOUTH, BBVA, STI, BBT, WFC".

can someone please suggest me a formula for this task?:confused:
:confused:
 
Last edited by a moderator:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
I have a excel file and value in Column A1 is "BAC (21-40%),WFC (21-40%),JPM (0-20%)" and value in

Column B1 is"AMSOUTH (N/A), BBVA (N/A),STI (N/A),BBT (N/A),WFC (N/A)"

i want to do text to column and concatenate the column A1 & B1 to get the final output in

Column C1 as "BAC, WFC, JPM, AMSOUTH, BBVA, STI, BBT, WFC".

can someone please suggest me a formula for this task?:confused:
:confused:
 
Upvote 0
[TABLE="width: 1049"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BAC ,WFC ,JPM[/TD]
[TD]AMSOUTH , BBVA ,STI ,BBT ,WFC[/TD]
[TD]BAC ,WFC ,JPM ,AMSOUTH , BBVA ,STI ,BBT ,WFC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]this macro does the business[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]if you find anything other than (,),-,numbers 0 to 9, ( and )[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]just add to the macro[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cells.Replace What:="(N/A)", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="%)", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="(", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="1", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="2", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="3", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="4", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="5", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="6", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="7", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="8", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Replace What:="9", Replacement:="", LookAt:=xlPart, _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SearchOrder:=xlByRows, MatchCase:=False[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] End Sub[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you're just looking to remove the brackets and their contents, highlight the area of the spreadsheet where you want this done and use Ctrl+H (Replace). Enter (*) in the Find box and leave the Replace box blank, then click Replace All.

It would be possible to write a formula, but would be quite lengthy, particularly if the number of bracketed sections could vary.
 
Upvote 0
Another macro for you to consider ..

Rich (BB code): 
Sub nikhil()
  With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(.Offset(, -2).Address & "&"", ""&" & .Offset(, -1).Address)
    .Replace What:=" (*)", Replacement:=""
  End With
End Sub
 
Upvote 0
Peter you are Awesome! Your code is Magnificient! Simplicity is the best beauty that's what i can say about your code. Thanks a TON for your help :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Formula to show in 4 columns the letters from one column with condition
Replies
6
Views
311
Fractalis
F
S
  • Solved
Line Chart Issue
Replies
3
Views
454
Fluff
Fluff
E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
297
Fluff
Fluff
L
  • Question Question
Help with formula please
Replies
0
Views
671
LucreMoneyWonga
L
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
226
Pbobas
P

Forum statistics

Threads
1,225,156
Messages
6,183,240
Members
453,152
Latest member
ChrisMd

Share this page

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