Creating Multiple Conditions for 1 column

[cmiali]

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

 

[agihcam]

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]

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]

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]

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]

Hi,

regardless of the items which are in column A, all items will be converted to their initials with this 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]

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]

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 ?
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
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")