Parsing problem with downloaded data

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
SAY THIS TEXT STRING IS IN A1 THEN B1

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

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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.
 

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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

Well-known Member
Joined
Sep 3, 2002
Messages
675

ADVERTISEMENT

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

Board Regular
Joined
Jul 11, 2002
Messages
148
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

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

Board Regular
Joined
Jul 11, 2002
Messages
148
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.
 

Forum statistics

Threads
1,143,613
Messages
5,719,713
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top