CODEWITHSUNDEEP
google-sheets
sakworld
sakworld

Reputation: 77

Google Sheet to SPLIT and STRIP

I am splitting the content of a Google sheet cell (C2) which has the contents as below: using =SPLIT(C2,CHAR(10))

Work Order number: 1157
Item: 0.16/50/100
Shift knots: 7700
Shift weight: 6.300
Downtime: 0

That will SPLIT those values into into 4 cells in C4, D4, E4, F4 and then I strip out the leading characters like below:

Item: from C4 using =RIGHT(C4, LEN(C4)-6), 
Shift knots: from D4 using =RIGHT(D4, LEN(D4)-13), 
Shift weight: from E4 =RIGHT(E4, LEN(E4)-14) and 
Downtime: from F4 using =RIGHT(F4, LEN(F4)-10)

What I would like to ask you is if there is a better way to achieve values like 0.16/50/100, 7700, 6.300, 0 split up across cells from 1 cell? which at the moment I am doing as a 2 step process.

Upvotes: 0

Views: 379

Answers (1)

JPV
JPV

Reputation: 27282

Try

=ArrayFormula(regexreplace(split(C2, char(10)), ".*:\s",))

and see if that helps ?

enter image description here

EDIT: If the data is 'horizontal', you could use the same approach but split the cell using the comma as the delimiter.

=ArrayFormula(regexreplace(split(C5, ", ", 0), ".*:\s",))

enter image description here

References:

Upvotes: 1

Related Questions