0

Every day I need to do the following 70 times by hand:
+ Cell B1 > Cut (copy) everything up to BEFORE the last space, then add it to cell A1
+ Move to B2 > Cut (copy) everything up to BEFORE the last space, then add it to cell A2
+ Move to B3 > Cut (copy) everything up to BEFORE the last space, then add it to cell A3
...

A1 | B1| C1
First Name | Last Name X |Other Stuff

Should Result in :

A1 | B1 | C1

First Name Last Name | X | Other Stuff

Once First Name Last Name is in A1 same thing has to happen with the next line untill there is no more content.

Any way to script this?

2
  • There is certainly a way to script basic cut and paste, but it is really difficult to understand what you are doing. Please can you explain the reason for doing what you do and explain what you mean by "B1 > cut everything up to BEFORE the last space"?
    – CallumDA
    Jan 21, 2015 at 12:23
  • 1
    The reason to do this is the daily police report in Bolivian Hospitality. Every day we have to inform various Authorities about guest movements. So what we are looking at here is the way my PMS spits out yesterdays check-ins: First Names in one Row, Last Name in the second row, we add the bed number to the last name too. Now I need to get First and Last Name in the first row but keep the bed number in the 2nd row. The solution from the first answer does the trick for me for now.....
    – Ben La Paz
    Jan 22, 2015 at 16:30

1 Answer 1

0

I'll be using two extra columns for this solution, E and F.

In cell E1 type this formula:

=A1& " " & LEFT(B1,FIND("§",SUBSTITUTE(B1," ","§",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))-1)

In cell F1 type this formula:

=RIGHT(B1,LEN(B1)-FIND("§",SUBSTITUTE(B1," ","§",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))

Make sure there are no characters § in your data. If there are, replace that character in my formulas with another, weirder character (it's just a placeholder).

Drag the formulas down to cover all your data, and there you have what you asked for!

If you need to put it back over the original data just copy and use paste values over A1 and B1.

Credits: I borrowed part of this formula from user m4573r here: I need to find the last Space in a XLS Cell

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .