[Solved] Find all digits in string & paste to separate cells

[cul]

I have a string of data: /sell/cart/?add=621449007&ev=atc_br/sell/cart/?add=621185376&ev=atc_br/sell/cart/?add=610796287&ev=atc_br/sell/cart/?add=602499243&ev=atc_br/sell/cart/?add=602252521&ev=atc_br/sell/cart/?add=596509065&ev=atc_br/sell/cart/?add=595216785&ev=atc_br/sell/cart/?add=582606808&ev=atc_br/sell/cart/?add=571854349&ev=atc_br/sell/cart/?add=527007820&ev=atc_br

I want to extract all the digits (they're SKUs) into separate cells. I'm using =split(C4, "=&", true) which splits the numbers into every 3rd cell but returns stuff I don't need in the other 2 cells. The string can be any length. 0 SKUs, 5 SKUs, 150 SKUs. Is there a more efficient way?

Thanks!

[RusselB]

First off, can you please verify that you are using Open Office, and not some other port (eg: LibreOffice, NeoOffice)? The reason I ask, is that my version of Open Office (4.1.4) does not have a SPLIT function in Calc.
It may also be that your version of Calc, if you are using 3.1, contains functions (in this case SPLIT) that later versions no longer support.

[cul]

Apologies, this was actually done in google sheets because I'm using IMPORTXML. I thought the SPLIT tool was universal. I'm going to post a new topic for what I'm directly trying to achieve. I'm not sure calc can do live IMPORTXML so I'll read around and start a new topic if I get stuck.

[Lupp]

@RusselB: <edit>As in his other post viewtopic.php?f=9&t=92297 "cul" obviously referred to Google Sheets.</edit>
There is no SPLIT() in LibO Calc (and also not in Excel afaik). The related TEXTJOIN() was meanwhile implemented in LibO, but the corresponding SPLIT() is still missing. It is, however, rather simple to write a custom function for the purpose as AOO BASIC has a well usable Split() function since the stone age.

@cul: Would you explain please, what the third parameter of your SPLIT() (true placed there in your use case) is supposed to accomplish? (And: Use the semicolon for delimiting parametersin Calc!)

[Lupp]

I want to extract all the digits (they're SKUs) into separate cells.
...
The string can be any length. 0 SKUs, 5 SKUs, 150 SKUs.

The requests are incongruous. If you want to have the "separate cells" in a row in Calc the maximum number of usable cells is 1024. If you consider to use a column, the number is 1048576. Thats much but clearly finite.
However, Calc will not handle use cases with many thousands of extractable parts with reasonable efficiency, even if you resort to custom code for the missing SPLIT() function. Anyway a string supplied by a cell reference cannot be longer than 65535 characters...

If you can assure a maximum number of, say, 200 extractable parts e.g. you may experiment with the following code:

Code: Select all

REM  *****  BASIC  *****

Option Explicit

Function getPartsOfBetweenAnd(pText, pLeft, pRight, Optional pMinCount As Long, Optional pPH As String)
REM This version assumes a syntax for the parts to recognize that does NOT accept
REM the delimiters pLeft or pRight INSIDE of one of the parts.
REM Let me call this an anti-greedy behaviour. The shortest parts left delimited by
REM pLeft and right by pRight are accepted and cut out. Empty parts allowed. 
REM Additional delimiters are disregarded.
REM In case of conflict an occurrence of pLeft will be accepted even if a right part of it
REM is usable to recognize an occurrence of pRight.
REM For usage with output to a locked array it may be necessary to allocate trailing cells
REM only needed if a changing input gives more parts. Use pMinCount in this case. 
  Dim parts, u As Long, n As Long, j As Long, h As Long
If IsMissing(pMinCount) Then pMinCount = 1
If IsMissing(pPH) Then pPH = "<none>"
parts    = Split(pText, pLeft)
u        = Ubound(parts)
n        = 0
For j = 0 To u
  h        = Instr(parts(j), pRight)
  If h>0 Then 
    parts(n) = Mid(parts(j), 1, h - 1)
    n        = n+1
  End If
  parts(j) = "" 
  REM No conflict! The original parts(0) cannot match. 
Next j
u        = n -1
If u<pMinCount-1 Then u = pMinCount-1
Redim Preserve parts(0 To u) 
For j = n To u
  parts(j) = pPH
Next j
getPartsOfBetweenAnd = parts
End Function

How the efficiency compares with google's stroke of genius, I cannot tell.

[MrProgrammer]

I have a string of data:

/sell/cart/?add=621449007&ev=atc_br/sell/cart/?add=621185376&ev=atc_br/sell/cart/?add=610796287&ev=atc_br/sell/cart/?add=602499243&ev=atc_br/sell/cart/?add=602252521&ev=atc_br/sell/cart/?add=596509065&ev=atc_br/sell/cart/?add=595216785&ev=atc_br/sell/cart/?add=582606808&ev=atc_br/sell/cart/?add=571854349&ev=atc_br/sell/cart/?add=527007820&ev=atc_br

I want to extract all the digits (they're SKUs) into separate cells.

If the strings are in A2 A3 A4 …, in B2 put =SUBSTITUTE(SUBSTITUTE(A2;"/sell/cart/?add=";"");"&ev=atc_br";CHAR(9)) and fill the formula down the column. Select column B by clicking the column header. Data → Text to Columns → OK. As previously advised you'll have trouble with more than about a thousand SKUs per string. For additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[cul]

Thanks MrP, thank you too to Lupp. No serious danger of 1000+ SKUs (unfortunately), so this will work.

There is technically a possibility of 8 or 10 digit SKUs but since I didn't mention that this definitely solves the problem for right now.

[RusselB]

MrProgrammer's solution will work no matter the length of the SKU, even if they are different lengths

[cul]

I must have misunderstood what CHAR(9) did, thanks, it does work on all SKU lengths.

edit> I see, CHAR(9) = display tab. I thought it was a counting formula.