Creating Multiple Conditions for 1 column

cmiali

New Member
Joined
Oct 13, 2006
Messages
3
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
Joined
Jan 16, 2006
Messages
1,624
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
Joined
Oct 13, 2006
Messages
3
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
Joined
Jan 16, 2006
Messages
1,624
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
Joined
Jun 1, 2003
Messages
130

ADVERTISEMENT

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
Joined
Feb 1, 2003
Messages
17,832
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

'START WITH
'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
Joined
Oct 13, 2006
Messages
3
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
Joined
Feb 1, 2003
Messages
17,832
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
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T




programmer ? :) :unsure:
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 :LOL:
that's how I learned it: just to encourage you ... you don't need to be a programmer
2. browse the menus and all submenus to see the stuff
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")
 

Forum statistics

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