remove numbers from text string

[bnv4487]

Hello,
If A1 = abcd 1234 abcdefg and A2 = abcd 123456 abcdefghi how do I get B1 = 1234 and B2 = 123456. the Mid formula will not work because they are different lengths.
Thank you,

 

[Joe4]

Use the FIND function to find where the space is, and then use MID based on that:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

 

[Peter_SSs]

Does the number always follow the first space? If so, try

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))+0

 

[bnv4487]

I don't believe either of those will work because there is no space with the last part of the text string.
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=198 border=0><COLGROUP><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 91pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" width=121 height=20>Column1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 58pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" width=77>Column2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>abc 12345:abcdefg</TD><TD id=td_post_2844823 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>12345

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>abc 123:abcd</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>123</TD></TR></TBODY></TABLE>

 

[Peter_SSs]

... there is no space with the last part of the text string.

Well there was in your original sample data!

Try this instead.

=REPLACE(LEFT(A1,FIND(":",A1)-1),1,FIND(" ",A1),"")+0

 

[Joe4]

In fairness, that was not the original question you asked.
You can see now why it is important to list all the details so we can give you a solution that will work for your situation.

If the examples provided in your last reply really are indicative of what your data looks like, this formula should work:

=MID(A1,FIND(" ",A1)+1,FIND(":",A1)-FIND(" ",A1)-1)