seenai

Board Regular
Joined
Mar 31, 2013
Messages
54
Hi,

I need to extract Text from a cell to different columns.

Eg : a cell is containing below text
  • S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378

I need the TEXT starting IN1 in different columns. (Character Length : 10)

Here my output in different columns is as below
TextColumn 1 Column 2Column 3Column 4
S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378IN17001671 IN13022537 IN13022385IN13022378

<tbody>
</tbody>

Help me to make a formulae for this.

Thanks in advance.

B.Srinivasa Rao
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I need the TEXT starting IN1 in different columns. (Character Length : 10)

Hi, here is one option, formula can be copied down and across as required.


Excel 2013/2016
ABCDEFGHI
2S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378 IN17001671 IN13022537 IN13022385 IN13022378IN17001671IN13022537IN13022385IN13022378IN17001671IN13022537IN13022385IN13022378
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(MID($A2,FIND("|",SUBSTITUTE($A2,"IN1","|",COLUMNS($B2:B2))),10),"")
 
Upvote 0
Hi,

I need to exclude Test starting with MIN1. Please help to update the formula. Eg :MIN13022385 not to be considered.

TextColumn 1Column 2Column 3
S/N:IN17001671BATCH:JAN/17 IN13022537Batch Mar 17 ,MIN13022385; IN13022378IN17001671IN13022537IN13022378

<tbody>
</tbody>
 
Upvote 0
need to exclude Test starting with MIN1.

Hi, here is one option:


Excel 2013/2016
ABCD
1TextColumn 1Column 2Column 3
2S/N:IN17001671BATCH:JAN/17 IN13022537Batch Mar 17 ,MIN13022385; IN13022378IN17001671IN13022537IN13022378
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(MID($A2,FIND("|",SUBSTITUTE(SUBSTITUTE($A2,"MIN","XXX"),"IN1","|",COLUMNS($B2:B2))),10),"")
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top