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
[Solved] How to write formula to extract parts of cell
[Solved] How to write formula to extract parts of cell
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
Apache OpenOffice 4.1.5
Windows 10 64-bit
Re: How to write formula to extract specific parts of a cell
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:
and extract text between the first and second space character, assuming source string is in A1:
TRIM is used to remove leading spaces, or multiple spaces, from the source string.
"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)
Code: Select all
=MID(TRIM(A1);FIND(" ";TRIM(A1))+1;FIND(" ";TRIM(A1);FIND(" ";TRIM(A1))+1)-FIND(" ";TRIM(A1)))
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: How to write formula to extract specific parts of a cell
Please update your signature in this forum.OOo 3.0.X on MS Windows Vista
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.
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.
Re: How to write formula to extract specific parts of a cell
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.
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
Apache OpenOffice 4.1.5
Windows 10 64-bit
Re: How to write formula to extract specific parts of a cell
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?
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: How to write formula to extract specific parts of a cell
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.
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
Apache OpenOffice 4.1.5
Windows 10 64-bit
Re: How to write formula to extract specific parts of a cell
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
Apache OpenOffice 4.1.5
Windows 10 64-bit
Re: [Solved] How to write formula to extract parts of cell
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.
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers