vba help please

[shyamvinjamuri]

split a name into first and last name based on upper and lower case.
eg. JohnDoe to read as John Doe.

Please help
Thanks

 

[mahmed1]

I fiddled around with it some, and there is probably a shorter way to do this, but the below options work.

Excel Workbook
	A	B	C
1	JohnDoe	John	Doe
2	MikeWilford	Mike	Wilford
3	JaniceDoe	Janice	Doe
4	MichaelWilford	Michael	Wilford
5	JohnDoe	John	Doe
6	MikeWilford	Mike	Wilford
7	JaniceDoe	Janice	Doe
8	MichaelWilford	Michael	Wilford
Sheet2

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

2 quick questions


=LEFT(A1,MAX(IF(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))<=90, IF(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))>=60,ROW(INDIRECT("A1:A"&LEN(A1))),0)))-1)

=LEFT($A3,SUMPRODUCT(--(CODE(MID($A3,ROW(INDIRECT("A1:A"&LEN($A3))),1))<=90)*--(CODE(MID($A3,ROW(INDIRECT("A1:A"&LEN($A3))),1))>=60)*(ROW(INDIRECT("A1:A"&LEN($A3)))))-2)


You used Sumproduct function and Max function, is this just another way of doing it or can't you just use the same code as above 1st or 2nd code all the way copied down?

2. Why do you need the second part of the formula?
IF(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))>=60

The formula work fine without this part, Are you doing this for any particular reason?

THANK YOU

 

[tushiroda]

1. I did it two different ways as an example, though you should pick a preferred way to go and stick with it for an entire column. In excel (as with almost anything) you can do things alot of different ways. There are usually better ways to do things though I don't think there is a big difference in the the 2 examples that I have.

2. the second part ensures that I find and split with a capital letter and not a non capital letter or symbol of some kind. If you know exactly what you're going to get data wise, you can simplify the formula, though this one is built to handle a wider variety of data. Also, with a wider variety of data that you would have to handle, the formula would get more complicated and you're probably better off trying to clean up your data in some way first or switching to vba to handle it.

 

[Peter_SSs]

In excel (as with almost anything) you can do things alot of different ways.

One different way for your C1 cell would be the simple non-array formula:

=SUBSTITUTE(A1,B1,"",1)

 

[shyamvinjamuri]

Thank you all so much.
Thank you hiker95, your code is exactly what I was looking for. Thanks again

 

[tushiroda]

One different way for your C1 cell would be the simple non-array formula:

=SUBSTITUTE(A1,B1,"",1)

doh, that is a much better way to do C1. :P