Quick excel help

[audi321]

Hi all, I have a really long test string in cell A1 (20000+ characters), and I want to extract certain elements from it into cells A2, A3, A4 etc etc

An example is in A1 I have the following
The quick brown fox jumps over the lazy dog

I need to extract the (for example) 5th to 8th characters into cell A2 (so this would contain 'quic') , the 12th to 15th characters into A3 (so this would contain 'rown'), the 18th to 22nd characters into A4, etc etc etc. you get the idea. If it helps, the really long text string repeats every 100 or so characters and I need the same numbers of characters from each repetition.

Any ideas? Is this a simple task for my basic knowledge? I did try google and it suggested splitting but the complexity I've added doesn't seem to work.

Thanks all for any advice.

Edited by audi321 on Wednesday 30th September 18:39

[Mr Pointy]

Have a look at the MID function:

https://support.microsoft.com/en-us/office/mid-mid...
https://exceljet.net/excel-functions/excel-mid-fun...
https://www.excelfunctions.net/excel-mid-function....

https://duckduckgo.com/?q=excel+mid+function&t...

[audi321]

Thanks I was just looking at 'mid' when you replied. I've actually made a mistake and the text does repeat itself exactly, meaning that the mid function doesn't work as the part of the text I need to extract is actually in different places each repetition.

If it helps, the text I need to extract is always 36 characters long and is not words (i.e. it looks like this 'd67fe141-d225-429e-84b2-b9fa309405aa') and it always follows a comma in the long text string

hmmmmm the plot thickens,

Edited by audi321 on Wednesday 30th September 18:39

[Troubleatmill]

=find(find what in quotes ,what cell, starting from)

[The_Nugget]

I’m not entirely sure of the string sequence from your description and it is hard without actually seeing it, but it sounds like you could:
Save the file as a txt file. Import the text file into excel and de-limit it using either commas or hyphens. That will turn you text into columns. You might have to delimit by commas and then delete the columns you don’t want and then repeat delimiting by hyphens.

[bigandclever]

I’d look at Text to Column on the cell using the comma as the delimiter. Then Transpose column to Row.
Then you’d have a load of cells in rows that all start at the same place and then you can just pick the first 36 characters with LEFT(cell,36) or whatever it is.

Edited by bigandclever on Wednesday 30th September 19:04

[audi321]

So the full string looks something like this (all in cell A1):

The quick brown fox jumps over the lazy dog ,d67fe141-d225-429e-84b2-b9fa309405aa/ The quick brown rabbit jumps over the lazy dog ,d67fe141-d225-429e-84b2-b9fa123456aa/ The quick brown rabbit jumps over the lazy horse ,d67fe141-d225-429e-84b2-b9fa309405aa/ etc etc

And its the 'd67fe141-d225-429e-84b2-b9fa309405aa' parts that I need extracting into cells A2, A3, A4 etc etc.

I can do it in anything, doesn't have to be excel.

[audi321]

bigandclever said:

I’d look at Text to Column on the cell using the comma as the delimiter. Then Transpose column to Row.
Then you’d have a load of cells in rows that all start at the same place and then you can just pick the first 36 characters with LEFT(cell,36) or whatever it is.

Edited by bigandclever on Wednesday 30th September 19:04

Perfect! Text to column and comma as the delimiter works a treat. Then =Left(A2,36) works perfectly!

Thanks all. I honestly thought this was going to be a nightmare.

[bigandclever]

Cool, I’ve no doubt there are more exotic and elegant ways of doing it but sometimes you just want to get the data out and go to the pub, right?