[Solved] Number Extraction From Cell Problem

[SandidgeHayLLC]

I Am Having Some Trouble With A Extraction From Mixed Cells.
I Use A Spreadsheet For Work Where I Can Paste Data In And Sort Through It.
Unfortunately Now I Have Found A Error Which Renders My Sheet Useless When The Cell
I Extract Data From Contains A Comma.

For Example The Cell May Say 296 (NW) And I Get 296 From It Perfectly (Exactly What I Need)
But When The Cell Contains Higher Numbers Over 1,000 Like 1,251 (NW) I Only Get A Single 1 Not 1251

Some Of These Cells Do Contain A Lot Of Text And Some Do Not.

The Formula I Am Using Is Below And I'm Including A Generic Copy Of The Sheet Similar To What I Use So You Can Look And See What's Happening. Any Help Sorting This Issue Out Is Greatly Appreciated!

=VALUE(MID(K17;SEARCH("[0-9]";K17;1);SEARCH("[^0-9]";K17;SEARCH("[0-9]";K17;1))-SEARCH("[0-9]";K17;1)))


I Am Running Open Office 3.3.0 On Windows 7 32 And 64 Bit. I Can't Upgrade As Many Of My Documents Corrupt Under New Versions. I Eventually Plan To Upgrade And Create All New Documents But Until Then I'm Stuck.

[FJCC]

I notice that in column K there is always a space after the number, so I would use

Code: Select all

=VALUE(LEFT(K17,SEARCH(" ",K17,1)-1))

as the formula in column P.

[SandidgeHayLLC]

I notice that in column K there is always a space after the number, so I would use

Code: Select all

=VALUE(LEFT(K17,SEARCH(" ",K17,1)-1))

as the formula in column P.

Unfortunately On The Sheet I Tried This And Get The Dreaded Err:508

[Villeroy]

Replace comma with semicolon.

[SandidgeHayLLC]

Replace comma with semicolon.

Dang I Didn't Even Notice That! Thank You. Is That Comma Just A Microsoft Thing Versus Open Office?

[Lupp]

Use the semicolon for separating parameters!

Unfortunately On The Sheet I Tried This And Get The Dreaded Err:508

Err: 508 means "unpaired parentheses".

Code: Select all

=IF(K17="";"";VALUE(SUBSTITUTE(LEFT(K17;FIND("(";K17)-1);",";"")))

should do better and will work independent of the separators used for numbers in the locale as long as not a decimal separator is used.

Avoid commas and points as well as group separators. They are explicitly deprecated by ISO 31-0 Sect. 3.3.

[Villeroy]

Excel and LibreOffice (the successor of OpenOffice) use semicolon as list separator when comma is used as decimal separator and comma when decimals are written with a point.
OpenOffice consistently uses the semicolon as list separator.