Pulling certain characters from strings of data regardless of the amount of characters

[joes2580]

Hi,

For example, I have a string of data that I need to extract info from but I can't use text to columns. So example, I have "Manual check No: 00000 Check date 1/1/2001" so in A1 for example (say the info I am extracting from is in C1) I want just the "00000" and in cell B1 I want just the "1/1/2001". I did try the MID function but it doesn't work all the way down the sheet because at certain points the Manual check No changed to the string " Check No:". Appreciate any assistance.

 

[Scott Huish]

Formula in B1: =IFERROR(TRIM(REPLACE(REPLACE(A1,FIND("Check date",A1),255,""),1,FIND(":",A1),"")),"")
Formula in C1: =IFERROR(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))+0,"")
Format C1 as date

Copy down.

Excel 2010
	A	B	C
1	Manual check No: 00000 Check date 1/1/2001	00000	1/1/2001
2	Check No: 32 Check date 1/1/2001	32	1/1/2001
3	Check No: 327967 Check date 12/11/2017	327967	12/11/2017
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(REPLACE(REPLACE(A1,FIND("Check date",A1),255,""),1,FIND(":",A1),"")),"")
C1	=IFERROR(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))+0,"")