Extracting Data based om Condition

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
Hello all, i want to extract data from column A to column B using formula. If number of characters after the hyphen is less than or equal to 4 then it has to be copied to respective B column cell. Refer example. Thank you.

Book2
AB
1HeadingExtracted Data
2AAAAAA-AAAAAAAA-AA
3BBBB-BBBBBB-BB
4CCCCC-CCCCC
5DDDDDD-DDDDDDDDD-DDD
6EEE-EEEEE-EE
7FFFF-FFFFFF
8GGGG-GGGGGGGG-GGGG
Sheet1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,241
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want.
Formula for B2

Excel Formula:
=IFERROR(IF(LEN($A2)-FIND("-",$A2)<=4,$A2,""),"")
 
Solution

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,152
Office Version
  1. 2016
Platform
  1. Windows
Hi Madhuchelliah,

Looks like we all came up with pretty much the same answer.

Madhuchelliah.xlsx
AB
1HeadingExtracted Data
2AAAAAA-AAAAAAAA-AA
3BBBB-BBBBBB-BB
4CCCCC-CCCCC 
5DDDDDD-DDDDDDDDD-DDD
6EEE-EEEEE-EE
7FFFF-FFFFFF 
8GGGG-GGGGGGGG-GGGG
9 
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=IFERROR(IF(LEN(A2)-FIND("-",A2)<=4,A2,""),"")
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

See if this does what you want.
Formula for B2

Excel Formula:
=IFERROR(IF(LEN($A2)-FIND("-",$A2)<=4,$A2,""),"")
Alex, It is working fine. Thank you
 

Forum statistics

Threads
1,147,475
Messages
5,741,347
Members
423,656
Latest member
Medrok2021

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
Top