# Split sentences into different columns using formulas

1. ## Split sentences into different columns using formulas

Dear all

I am trying to create a table with formulas built in to separate the words into different columns.

Refer to attached,
For example:
If I paste the following into E2,
11 Sep 2017: Inflow of USD\$1,000,000.00 via CHEQUE 123456 from ()

The following should generate automatically
11 Sep 2017 --> Column A2 --> =TRIM(LEFT(E2,FIND(":",E2)-1))
Inflow --> Column B2 --> =IF(ISNUMBER(SEARCH("Inflow",E2)),"Inflow","Outflow")
123456 --> Column C2
USD\$1,000,000.00 --> Column D2 --> =TRIM(MID(E2,FIND("of",E2)+2,FIND("via",E2)-FIND("of",E2)-2))

I have problems with the Cheque No. I am trying to find a formula that can find the position of the numeric value after the "via" and take 6 digits from there.

1) The position of the cheque no is not fixed. It varies because of the amount and inflow/outflow.
2) The cheque number will always be a 6 digits number
3) It is the only numeric value after the "via"

Appreciate if anyone has a solution to it.

Thanks and warmest regards,
Fantasylo

2. ## Re: Split sentences into different columns using formulas

Not too pretty but it works for the example given:

=MID(E2,FIND(IF(COUNTIF(E2,"*DEPOSIT*"),"DEPOSIT","CHEQUE"),E2)+IF(COUNTIF(E2,"*DEPOSIT*"),8,7),6)

3. ## Re: Split sentences into different columns using formulas

Happy to help. Thanks for the rep!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1