Separate string of text into 3 columns

**LMills77** · 03-07-2021, 10:00 AM

Hi all,

I'm curious how I can split the text below into three separate columns. Here's the text:

04 Jan 21T K MAXX£10.27

Column 1 - 04 Jan 2021
Column 2 - T K Maxx
Column 3 - £10.27

Would there be a formula that could do all this?

Many thanks for your help,

Lee.

**BadlySpelledBuoy** · 03-07-2021, 10:04 AM

Do the values always follow the same format with a "dd mmm yy" date format at the start, a price with a £ at the end and other stuff in between?

BSB

**Glenn Kennedy** · 03-07-2021, 10:13 AM

One way (maybe...)

=LEFT(A2,9)+0 (format as date)

and

=MID(A2,10,SEARCH("£",A2)-10) (format as General)

and

=MID(A2,SEARCH("£",A2)+1,255)+0 (format as currency)

**Glenn Kennedy** · 03-07-2021, 10:13 AM

with file...

**LMills77** · 03-07-2021, 11:11 AM

That's fantastic - works perfectly.

One issue cropped up which the formula didn't pick up on were minus payments. Could a formula pick those up and add the '-' in there too?.

Thanks for your help,

Lee.

**Glenn Kennedy** · 03-07-2021, 11:20 AM

How are they designated £-100.00 or as -£100.00?

**Glenn Kennedy** · 03-07-2021, 12:20 PM

Please post a sample Excel file. I want to see if there is a CHAR(10) in between the date/name/amount sections.

Instructions are in yellow banner, top of page.

**LMills77** · 03-07-2021, 12:21 PM

Originally Posted by Glenn Kennedy

How are they designated £-100.00 or as -£100.00?

They are designated as -£12.00.

Lee.

**Glenn Kennedy** · 03-07-2021, 12:22 PM

OK. So, a sample file with a few REAL examples, please.

**Glenn Kennedy** · 03-07-2021, 12:32 PM

I would STILL prefer to see some real values... uncorrupted by the Forum's occasionally bizarre thread formatting... but this works:

B2:
=LEFT(A2,9)+0 format as date.

C2:
=MID(A2,10,IFERROR(SEARCH("-£",A2),SEARCH("£",A2))-10) format as General

D2:
=MID(A2,SEARCH("£",A2)+1,255)*IF(ISNUMBER(SEARCH("-£",A2)),-1,1)

copied down...

**Glenn Kennedy** · 03-07-2021, 12:32 PM

Duhh. forgot the file... again.

**LMills77** · 03-07-2021, 12:36 PM

Amazing - many thanks for your help in this. It would have taken hours to work that out on my own. You've saved me a lot of time and hassle.

Thanks again,

Lee.

**Glenn Kennedy** · 03-07-2021, 12:38 PM

You're welcome... thanks for the rep, but remember to post a sample sheet next time. It makes everything SO much easier...

**LMills77** · 03-07-2021, 12:41 PM

Originally Posted by Glenn Kennedy

You're welcome... thanks for the rep, but remember to post a sample sheet next time. It makes everything SO much easier...

OK, thanks. I'll keep that in mind to post sheets.

Lee.

Separate string of text into 3 columns

LinkBack

Thread Tools

Rate This Thread

Display

Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Re: Separate string of text into 3 columns

Thread Information

Users Browsing this Thread

Similar Threads

Macro to separate long string of characters and several spaces into separate columns

[SOLVED] Formula or function to separate string of text in a cell to multiple columns

[SOLVED] Splitting Text String Into Separate Columns

How to split text from text string into separate columns...no delimiters!

[SOLVED] Separate numeric/text combination into two separate columns.

[SOLVED] How to separate a text string into two columns?

Separate a FirstLast text string into two columns

Bookmarks

Bookmarks

Posting Permissions