Formula to extract 5 or 6 digits based on the text

[shre0047]

I have a column with similar cells but spacing around the colon varies.

ID1: 52685 ID2: 83145 ID3: 52973 ID4: 84814 Name: Kevin

<tbody>
</tbody>

FOr one formula, I want to find ID1, then extract the number 52685 which I have the following:
=MID($A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A2&"0123456789")),5)
How do I conduct it if it's 6 characters?

The other formula, I want to find ID4, and extract number 84814

 

[Joe4]

Do you just want to return whatever is after the colon?
If so, then just use:

=TRIM(MID(A2,FIND(":",A2)+1,LEN(A2)))

 

[Scott Huish]

A different way and this won't error if there's no colon:

=TRIM(REPLACE(A1,1,FIND(":",A1&":"),""))

 

[shre0047]

Within the entire cell is all the IDs/Name with line breaks, so if TRIM was utilized, it would only pull the first digits from ID1 and not ID4

 

[Joe4]

Within the entire cell is all the IDs/Name with line breaks,

That wasn't clear from the original post.

 

[shre0047]

That wasn't clear from the original post.

Apologies as I tried to put the entire cell information into 1x1 table but I had the grid set to no border

ID1: 52685 ID2: 83145 ID3: 52973 ID4: 84814 Name: Kevin

<tbody>
</tbody>

 

[Joe4]

Personally, I do not like working with poorly formatted data.
Are you opposed to a macro that splits it out multiple rows?
And maybe splits it into multiple columns too?
Then you would be able to easily use the VLOOKUP formula on it.

We would just need to understand the layout of the data. Is there just one cell like this (A2)?
Is there anything below this in your file?

 

[jtakw]

Hi,

Assuming you want the extracted values Across columns in individual cells...

If you Only want the IDs extracted, use B1 formula copied across.
If you want IDs And Name extracted, use B2 formula copied across.

Book1
	A	B	C	D	E	F
1	ID1: 526859 ID2: 83145 ID3: 52973 ID4: 84814 Name: Kevin	526859	83145	52973	84814
2	ID1: 526859 ID2: 83145 ID3: 52973 ID4: 84814 Name: Kevin	526859	83145	52973	84814	Kevin
Sheet650

Cell Formulas
Range	Formula
B1	=IFERROR(1/(1/TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,":",REPT(" ",255)),CHAR(10),REPT(" ",255)),(COLUMNS($B1:B1)*2-1)*255,255))),"")
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,":",REPT(" ",255)),CHAR(10),REPT(" ",255)),(COLUMNS($B2:B2)*2-1)*255,255))

 

[DanteAmor]

If you put the headers you can get the data

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:111px;" /><col style="width:57px;" /><col style="width:75px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#92d050; text-align:center; ">ID1</td><td style="background-color:#92d050; text-align:center; ">ID2</td><td style="background-color:#92d050; text-align:center; ">ID3</td><td style="background-color:#92d050; text-align:center; ">ID4</td><td style="background-color:#92d050; text-align:center; ">Name</td></tr><tr style="height:91px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ID1: 52685<br />ID2: 83145893<br />ID3: 52973<br />ID4: 848146<br />Name: Kevin</td><td style="text-align:center; ">52685<br /></td><td style="text-align:center; ">83145893<br /></td><td style="text-align:center; ">52973<br /></td><td style="text-align:center; ">848146<br /></td><td style="text-align:center; ">Kevin</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"Name",REPT(" ", 100) & "Name"),"ID",REPT(" ",100)&"ID"),":",""),B$1,REPT(" ",100)),COLUMN()*100,100))</td></tr><tr><td >C2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"Name",REPT(" ", 100) & "Name"),"ID",REPT(" ",100)&"ID"),":",""),C$1,REPT(" ",100)),COLUMN()*100,100))</td></tr><tr><td >D2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"Name",REPT(" ", 100) & "Name"),"ID",REPT(" ",100)&"ID"),":",""),D$1,REPT(" ",100)),COLUMN()*100,100))</td></tr><tr><td >E2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"Name",REPT(" ", 100) & "Name"),"ID",REPT(" ",100)&"ID"),":",""),E$1,REPT(" ",100)),COLUMN()*100,100))</td></tr><tr><td >F2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"Name",REPT(" ", 100) & "Name"),"ID",REPT(" ",100)&"ID"),":",""),F$1,REPT(" ",100)),COLUMN()*100,100))</td></tr></table></td></tr></table> <br /><br />

 

[shre0047]

Thanks for the recommendations!