Formatting text

[Hockyplr]

I have been looking for the answer on the boards but can't seem to find it.

What I am trying to do is this:

I have a VBA button that allows someone to input a name, this name is pre-formatted like this:

Lastname, Firstname

That is copied to a hidden column and used to pull data from a seperate excel document. The hidden column is B.

In Column C, I would like to use the information in Column B, but have it auto populate, and change the format to:

Firstname Lastname

That way the user, when setting up the spreadsheet, only needs to copy and paste the pre-formatted text in the input box and hit enter, and of course rinse and repeat for however many people they are placing on the sheet.

Thank you in advance!

 

[Scott Huish]

See here:
http://www.mrexcel.com/board2/viewtopic.php?t=284222

 

[Hockyplr]

Excellent! Thank much, works perfectly.

Did have one further question, unless there is an actual value, I get #VALUE! in every cell. Is there a way to do a check and if there is indeed no value in that cell, have it display a blank cell, yet continue to hold the formula I used in my original problem?

=TRIM(MID(B9&" "&B9,FIND(",",B9)+1,LEN(B9)))


Thanks again!

This board rules!

 

[Scott Huish]

=TRIM(MID(B9&" "&B9,FIND(",",B9&",")+1,LEN(B9)))