# Excel Formula

#### kipper19

##### Board Regular
Looking for some help here
In cell A1 I have the number 2, and cell B1 I have the name John (B)
In cell A2 I have the number 3, and cell B2 I have the name Jim
In cell A3 I have the number 4, and cell B3 I have the name June (B)

I'm looking for a formula to find names with the (B) after them then copy the number and name to a different cell etc

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Maybe like this

Excel 2007
ABC
12John(B)John(B) 2
23Jim
34June(B)June(B) 4
Sheet1
Cell Formulas
RangeFormula
C1=IF(RIGHT(B1,3)="(B)",B1&" "&A1,"")
C2=IF(RIGHT(B2,3)="(B)",B2&" "&A2,"")
C3=IF(RIGHT(B3,3)="(B)",B3&" "&A3,"")

Michael, yes that would work but I am looking at scanning lots of data in the column B etc, then when it finds names with the (B) copies the names etc in order in other cells B17, B18 etc,

I think you'll need to use VBA for this !!!

Ok, thanks anyway Michael for your response

Are you happy with a VBA solution ???

Michael, yes certainty if you maybe able to help

Try

Code:
``````Sub MM1()
Dim lr As Long, r As Long, x As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 6
For r = 1 To lr
If InStr(Range("B" & r).Value, "(B)") Then
Range("A" & r & ":B" & r).Copy Range("A" & lr + x)
x = x + 1
End If
Next r
End Sub``````

May be
Code:
``````Sub Test()    Dim arr     As Variant
Dim i       As Long

arr = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = LBound(arr, 1) To UBound(arr, 1)
If InStr(arr(i, 2), "(B)") > 0 Then
arr(i, 3) = arr(i, 2) & " " & arr(i, 1)
End If
Next i
Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value = arr
End Sub``````

Hi kipper,

you can do this with a single array formula.

={IFERROR(INDEX(YourRange,SMALL(IF(NOT(ISERROR(SEARCH("(B)",YourRange))),ROW(A1:A11),""),ROW(A1:A11))),"")}

Using Ctrl+Shift+Enter

YourRange refers to the range of the original list (In your example: B1:B11).
ROW(A1:A11)is used to get an array that contains numbers from 1 to n (n being the number of rows from your list, 11) {1,2,...,n}

I will try to explain how the formula works by separating arguments in different lines and inserting coments in green. It may help to start from the inside and work your way out.

=
{IFERROR( this will avoid getting a bunch of #N/A's after retrieving the values you are looking for
INDEX( this will return the contents of the cells you want
YourRange,
SMALL( this will be used to select only the rows where "(B)" is present in the correct order
IF( this will evaluate each element of your TRUE/FALSE array
NOT( this will convert your array so that TRUE means that "(B)" is present
ISERROR( this will turn all numers into FALSE and all #N/A's into TRUE, leaving an array where TRUE means that "(B)" is not present
SEARCH( this will return the place where "(B)" can be found in each cell or #N/A if it cannot be found
"(B)",
YourRange
)
)
),
ROW(A1:A11), this will turn every TRUE to the row number of the cell
"" and every FALSE to a blank value
),
ROW(A1:A11) this will make SMALL() retrieve the row numbers of the TRUE cells in the proper order
)
),
""
)}

Hope it helps. Regards,

Franz

Replies
15
Views
529
Replies
1
Views
220
Replies
1
Views
561
Replies
7
Views
401
Replies
10
Views
408

1,196,264
Messages
6,014,321
Members
441,814
Latest member
youngstubbs

### 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.

### Which adblocker are you using?

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

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