Parsing problem with downloaded data

[Sir Vili]

Hi.

I have parsing problem with downloaded data. There should not be any problem to use Text to Column system, but after every text line (= long sentence) in row there are more lines and some blank lines in rows before new text to be parsed.

I only need names.

Here is example which is in one row in Excel:

BERNDTSSON, MIKAELA 1400 0530 1330 0530 1430 1000 1430 1430 0530 1300 1400 1400 1430 1100 1430 1100

So this long sentence in one row and I need only to get name BERNDTSSON, MIKAELA.

I have tested with formula:
LEFT(C1;SEARCH(",";C1;1)-1), but it gives me only last name BERNDTSSON not whole name BERNDTSSON, MIKAELA

Have anybody any idea to do this with formulas?

Regards Sir Vili.

 

[Aladin Akyurek]

Sir,

Try:

=LEFT(C1,SEARCH("@",SUBSTITUTE(C1," ","@",2))-1)

 

[DonkeyOte]

SAY THIS TEXT STRING IS IN A1 THEN B1

=LEFT(A1,FIND(",",A1)-1)&" "&MID(A1,FIND(",",A1)+2,FIND(" ",A1))

 

[Sir Vili]

Hi.

Both worked fine.

Thank you very much for your replies, they are most sincerely appreciated. I knew that help is offered here.

BRGDS Sir Vili.

 

[PaddyD]

anotrher option, depending on the details of your data:

http://216.92.17.166/board/viewtopic.php?topic=16737&forum=2

paddy

 

[Sir Vili]

Hi PaddyD.

As You said: depending on the details of your data, it did not work completely (but nearly).

I thank You also for reply and that You gave Your time to my problem.

Regards Sir Vili.
This message was edited by Sir Vili on 2002-10-15 04:58
This message was edited by Sir Vili on 2002-10-15 04:58

 

[ChrisUK]

Another one for the pot!

I liked the replies so far to this one, but I wondered what would happen if you had two spaces between names or a double barreled surname (Anthony Smith Johnson or Ian St John).

So, I put on my thinking cap and came up with the solution below. the assumption I made is the data after the name seems to be timesheet info, so, I searched for the first occurance of a number (0,1,2 are the only ones in a 24hr clock!).

Heres the solution, entered as an array formula

=LEFT(A1,MIN(FIND("0",A1,1),FIND("1",A1,1),FIND("2",A1,1))-1)

Chris

 

[Sir Vili]

Hi Chris.

You are right: the data after the name seems to be timesheet info, as You said. Data is from Resource Planning System.

Sorry Chris but, Your formula gives me #Value!. Changing it so, that 0 is changed to , and 1 to " " and 2 to " ", it gives me last name, not the whole name. All other variations gives #Value! error message.

Personally I like variations to solve problems in Excel, because they helps me to understand more and more Excel formulas.

Thank You also for Your attention to my problem.

Regards Sir Vili.

 

[ChrisUK]

Works just fine with me .... did you enter the formula or Control+Shift+Enter it ?

You MUST Control+Shift+Enter it or it will give the #VALUE error

Chris

 

[Sir Vili]

Hi Chris.

Yes I used Control+Shift+Enter. I copied Your formula to different cells to check it and I found that once it worked. It not working in every cell, but I did not tested with all names because there are hundreds of names.

Regards Sir Vili.