Extracting Data based om Condition

[madhuchelliah]

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
	A	B
1	Heading	Extracted Data
2	AAAAAA-AA	AAAAAA-AA
3	BBBB-BB	BBBB-BB
4	CCCCC-CCCCC
5	DDDDDD-DDD	DDDDDD-DDD
6	EEE-EE	EEE-EE
7	FFFF-FFFFFF
8	GGGG-GGGG	GGGG-GGGG
Sheet1

 

[Alex Blakenburg]

See if this does what you want.
Formula for B2

=IFERROR(IF(LEN($A2)-FIND("-",$A2)<=4,$A2,""),"")

 

[steve the fish]

Try:

=IF(LEN(A2)-FIND("-",A2)>4,"",A2)

 

[Toadstool]

Hi Madhuchelliah,

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

Madhuchelliah.xlsx
	A	B
1	Heading	Extracted Data
2	AAAAAA-AA	AAAAAA-AA
3	BBBB-BB	BBBB-BB
4	CCCCC-CCCCC
5	DDDDDD-DDD	DDDDDD-DDD
6	EEE-EE	EEE-EE
7	FFFF-FFFFFF
8	GGGG-GGGG	GGGG-GGGG
9
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(IF(LEN(A2)-FIND("-",A2)<=4,A2,""),"")

 

[madhuchelliah]

See if this does what you want.
Formula for B2

=IFERROR(IF(LEN($A2)-FIND("-",$A2)<=4,$A2,""),"")

Alex, It is working fine. Thank you

 

[madhuchelliah]

Try:

=IF(LEN(A2)-FIND("-",A2)>4,"",A2)

Steve, yours too done the job. Thanks for your time.

 

[Alex Blakenburg]

Glad we could help and thanks for letting us know.