String Manipulation

[TrentSteel]

Hello All

I don't think this is too hard, but my formula/VBA knowledge has proven insufficient so far. I have a approx. 1800 records in a Excel Spreadsheet like this:

User Name(col'm 1)
John-Smith.Unit.Domain.Country

I need to break apart the User Name string so it is this format

Col'm 1 Col'm 2
Smith, John Unit

The other data I don't need (Domain & Country). Also, once broken up, will Col'm 1 be searchable using VLookup, or will it return an error due to the formula/coding? I this is prob pretty basic, but it's been giving me a hard time.

TIA

 

[SkipVought]

Hi TS,

Try using the Text to Columns wizard in the Data menu.

Parse your data in steps first using the comma delimiter and then using a dash delimiter.

This should result in a column for each like thing: last, first, unit, etc

Then write a formula to put Last, First together

Finally if you want to, copy the results of the formula column and Paste Special/Values in the the same column and

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

The formula for your first column (assuming the data to change is in A1):

=LEFT(SUBSTITUTE(A1,"-",", &quot,FIND(".",A1))

The formula for the second column (assuming the same):

=MID(A1,FIND(".",A1)+1,ABS((FIND(".",A1)-FIND(".",A1,FIND(".",A1)+1)))-1)

I'm sure there is a better way to do the second column, but I can't think of it.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TrentSteel]

Skip/Bowser

This forum never fails to help out! Thanks again for two quick and easy solultions!

TS

 

[Bowers74]

I'm glad we could help!



Skip, I guess we can expect some stars to show up any time now. Right??





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Hey Mike - this isn't experts exchange ;-)
Personally, I don't like to mention that in my own threads but I'll quite happily chirp up when I have no other involvement in the thread:

TrentSteel, the way we generally say thanks in TT is to award a star for a helpful answer or some useful knowledge. This also helps those users who search the archives as it allows them to home in on those posts that have a good response. To award a star, simply click on the "Mark this post as a helpful / expert post" link at the bottom of the appropriate thread

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Adnane]

Col1: LEFT(A2,FIND(".",A2)-1)
Col2: LEFT(SUBSTITUTE(A2,B2&".","&quot,FIND(".",SUBSTITUTE(A2,B2&".","&quot)-1)