[Solved] How to write formula to extract parts of cell

Discuss the spreadsheet application
Post Reply
Louye
Posts: 19
Joined: Wed May 20, 2009 8:07 am

[Solved] How to write formula to extract parts of cell

Post by Louye »

Hello, I have a five sheet spreadsheet. The first four pages are blank. I will copy and paste data from other spreadsheets into these first four pages. The fifth sheet is where I extract specific bits of data from the first four sheets (along with some calculations). Finally, this fifth page is copied and pasted into another spreadsheet. After that, the process starts over again with new data. The first four pages are cleared out, and new pages are now copied and pasted into the first four pages.......
I need a formula solution to the following question as "text to columns" is not sufficient ("text to columns" would require me to go into the sheet each time and update, I need it to happen automatically). Here is a scenario:

Let's say that cell A1 of sheet five pulls cell A1 of sheet 2. Sheet five cell A1 now reads 69 4 6 10 -1.40
I would like two numbers (and this is a text string, not a number string) from cell A1. FIrst I would like 69 to appear in cell B1, and I would like 4 to appear in cell C1.

Each time this happens, and it will be hundreds of times a day, the pull is always the same, the first number and the second number. So, if cell A1049 read 507 28 36 45 -0.19 I would want 507 and 28 in cells B1049 and C1049 respectively.

Any help is greatly appreciated
Last edited by Louye on Thu May 28, 2020 8:33 am, edited 1 time in total.
Louye
Apache OpenOffice 4.1.5
Windows 10 64-bit
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to write formula to extract specific parts of a cell

Post by robleyd »

Your question seems to boil down to:

"I have a string with groups of characters separated by a space; I want to extract the characters before the first space, and the characters between the first and second space".

To extract text up to the first space character, assuming source string is in A1:

Code: Select all

=LEFT(TRIM(A1);FIND(" ";TRIM(A1))-1)
and extract text between the first and second space character, assuming source string is in A1:

Code: Select all

=MID(TRIM(A1);FIND(" ";TRIM(A1))+1;FIND(" ";TRIM(A1);FIND(" ";TRIM(A1))+1)-FIND(" ";TRIM(A1)))
TRIM is used to remove leading spaces, or multiple spaces, from the source string.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to write formula to extract specific parts of a cell

Post by Zizi64 »

OOo 3.0.X on MS Windows Vista
Please update your signature in this forum.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Louye
Posts: 19
Joined: Wed May 20, 2009 8:07 am

Re: How to write formula to extract specific parts of a cell

Post by Louye »

Thanks David,
The first part worked perfectly, however, on the second part (using the text in the example I gave), I am getting 4 6 as opposed to getting just 4.
Louye
Apache OpenOffice 4.1.5
Windows 10 64-bit
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to write formula to extract specific parts of a cell

Post by robleyd »

That example - 69 4 6 10 -1.40 - for me produces 4 as expected. Perhaps there is something in your string that isn't obvious.

Can you provide a small sample spreadsheet that illustrates the problem?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Louye
Posts: 19
Joined: Wed May 20, 2009 8:07 am

Re: How to write formula to extract specific parts of a cell

Post by Louye »

69 4 6 10 -1.40


I do not know how to provide a sample table, however, above is the text taken directly from the cell referenced. Perhaps the spaces themselves are throwing this thing off.
Louye
Apache OpenOffice 4.1.5
Windows 10 64-bit
Louye
Posts: 19
Joined: Wed May 20, 2009 8:07 am

Re: How to write formula to extract specific parts of a cell

Post by Louye »

I went to put together a sample spreadsheet, and it worked as advertised. I then went back to my actual spreadsheet, and it worked!! Don't know what the deal was, I typed it in twice and it did not work, and when I went back, it did. Thanks again. Out of curiosity, what do the "+1"s mean in the second formula?
Louye
Apache OpenOffice 4.1.5
Windows 10 64-bit
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] How to write formula to extract parts of cell

Post by robleyd »

FIND(" ";TRIM(A1))+1

FIND returns the start position in the string of the search string; this is used as the start location for MID to extract the required text. You want to extract from the next character after the space, so you need to add 1 to get the correct start location.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply