calc: Extract string from range of cells

1

I´m trying to figure out a easy to understand way to extract a string (that is in a range of cells) from a range of cells...

That is the range of cells I have to look for a specific string (in a range of possibilities)

A1
BRANCO POLIESTER (25X40CM) - UNIART

COLORIDO POLIESTER (42X42CM) - UNIART

TRIO LOSANGO 125X65 ARTESANAL  - MARROM - UNIART

ARTESANAL 30X130 - VERMELHO - UNIART

VERMELHO IMPRESSAO DIGITAL 30X30 - VERMELHO - UNIART

AQUARELADO TRIO 40X60 ARTESANAL - COLORIDO - UNIART

ALMOFADA TNT BRANCA 45X45CM - UNIART

TULIPAS 60X130 ARTESANAL - MARROM - UNIART

In the description there´s the size (where we find in another range of cells)

B1

size

25X40

42X42

125X65

30X130

30X30

40X60

45X45

60X130

70X100

70X120

20x60

50x80

What I want -- a colum with the size that matches the size in the Product_description something like this

C1

BRANCO POLIESTER (25X40CM) - UNIART              25x40

COLORIDO POLIESTER (42X42CM) - UNIART            42x42

And so on

Thanks for any help

Antonio Vasconcelos

Posted 2014-11-20T12:33:23.913

Reputation: 11

Does column B isolate the size information for each record or is it a separate table that is just a list of standard sizes? Are you looking to create in column C single entries that concatenate the size to the description or is this columns C and D? – fixer1234 – 2014-11-20T18:22:08.467

Answers

0

This solution will depend on you having the Tools ► Options ► OpenOffice Calc ► Calculate ► Use regular expressions in formulas CHECKED. Without this option enabled, you will get #VALUE! errors.

Using SEARCH with a regex mask can peel out the text that you want and some text manipulation can trim unwanted leftovers from the strip operation.

        Peel out number X number from string

The standard (non-array) formula in B1 is,

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(UPPER(A1); SEARCH("([:digit:]([:alpha:]|[:digit:]))"; A1); 9); "CM"; " "); ")"; " "); " "; REPT(" "; 9)); 9))

  1. The SEARCH function locates the <number><by><number> pattern and returns the ordinal position.
  2. That position is passed into the MID function as a starting point. MID takes a total of 9 characters from the starting point.
  3. Closing brackets and CM text is converted to single spaces.
  4. Single spaces are converted to 9 spaces.
  5. The left-most 9 characters are extracted and TRIM is applied to remove the trailing spaces.

Fill down as necessary.

Jeeped

Posted 2014-11-20T12:33:23.913

Reputation: 2 435

Asked: 2014-11-20T12:33:23.913

Viewed: 89 times

Active: 2014-12-24T03:17:31.013