# Creating Multiple Conditions for 1 column

#### cmiali

##### New Member
Hi - I have a file that I export out of an application into Excel and I need to be able to take all the values that is going to exist in one of these columns and programmatically convert it to initials (i.e. Jane Doe - to JD). I already know the names that can appear in here. I know Search and Replace can do it, but I've been asked if there is a faster way.

Can someone tell me if this can be done in Excel?

Thanks much.
cmiali

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### agihcam

##### Well-known Member
Hi-
Welcome to the board. Is there always a space between first name and last name?
also, does data always be first name and last name or it can have like this;

Mary Anne Hill

#### cmiali

##### New Member
Hi - There is always a space between the names. I have to get this from an extract out of an application, so I cannot reformat how the data is extracted.

#### agihcam

##### Well-known Member
try the formula;

=LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)

in a blank column then copy down.

copy and paste special values to the original column.

#### larrydunn

##### Board Regular

If you know all of the names that can appear, this could also be solved by VLOOKUP. Just put a side-by-side listing, with names and initials, in another sheet in the workbook, then use VLOOKUP to read your data into the sheet you're working in.

Naturally, this doesn't help you to get the initials in the first place, but it's another way of looking at the problem.

#### erik.van.geit

##### MrExcel MVP
Hi,

regardless of the items which are in column A, all items will be converted to their initials with this code
Code:
``````Option Explicit

Sub convert_to_initials()
'Erik Van Geit
'061014

'John Writer
'Mark Pencill von Ink

'To GET
'J W
'M P v I

Dim rng As Range
Dim arr As Variant
Dim LR As Long
Dim i As Long

LR = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & LR)

arr = rng

For i = 1 To rng.Rows.Count
arr(i, 1) = initials(arr(i, 1))
Next i

rng = arr

End Sub

Function initials(nm) As String
Dim splitName As Variant
Dim getInits As String
Dim i As Integer

splitName = Split(nm)

For i = LBound(splitName) To UBound(splitName)
getInits = getInits & Left(splitName(i), 1) & " "
Next i

initials = Left(getInits, Len(getInits) - 1)

End Function``````
kind regards,
Erik

#### cmiali

##### New Member
thanks everyone for all the tips. I have just a couple of more questions:
TO ERIK - you have to use VB to get that code in don't you?
TO ALL - what did you read/do to be able to figure this out? I assume you are all programmers, but are there books out there that help with this?

#### erik.van.geit

##### MrExcel MVP
you're welcome

yes, VBA
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the following code or your code:

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T

programmer ?
not at all, I'm a pianist-musician
how I learned VBA
1. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)
2. "step" through it with the function key F8, while observing what's going on on your sheet
3. click on an item and hit F1 to let popup the help
4. add some steps to see what's going on in your code
example
add: MsgBox "you're currently on this sheet :" & ActiveSheet.Name & Chr(10) & Cell(i, j).Address & "will be filled with the value" & tmp
or before running click in the line and hit F9 (inserting a breakpoint)

also:
1. visit this forum, browse & search and start to answer the questions instead of asking them
that's how I learned it: just to encourage you ... you don't need to be a programmer
3. browse the helpfiles in Excel-helpfiles ...

very important: be curious and stay curious
most important: PLAY with the stuff

(OK, programmers can do it better using some "inside-tricks")

Replies
0
Views
55
Replies
1
Views
231
Replies
5
Views
281
Replies
10
Views
295
Replies
4
Views
548

1,136,621
Messages
5,676,852
Members
419,656
Latest member
lironprofit

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