Extract Text After Space In String

[holmbjerg]

I need to extract text after space in string e.g.

INV1019469 Intrum Justitia Oy
INV1028 Petskiboat Oy
INV102812 Palo, Tolvanen & Al
INV103 Fast Capital Oy

Result:
Intrum Justitia Oy
Petskiboat Oy
Palo, Tolvanen & Al
Fast Capital Oy

Thanks
Lasse

 

[Rick Rothstein]

I need only 7..how do i achieve this..

Give this a try which returns the number as TEXT...

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

If you need the number to be an actual number (not TEXT), the try this instead...

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

 

[István Hirsch]

Try this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",15)),30,15))

 

[VBABEGINER]

Thanks Istavan, for your reply. I'm not some techie, but something looks good.


Hi Rick Sir, Thanks for your reply..

one more i require,

NOTS0021CA (09/03)

to pull Month im using this..i.e. "9"

=RIGHT(LEFT(RIGHT(B6,LEN(B6)-FIND(" ",B6)),3),1)

Dont understand, how to get, Year...I.e. "03"

=RIGHT(B6,3)-1

Im using this, but this giving me an error.."#Value!"

Give this a try which returns the number as TEXT...

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

If you need the number to be an actual number (not TEXT), the try this instead...

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

 

[Rick Rothstein]

to pull Month im using this..i.e. "9"

=RIGHT(LEFT(RIGHT(B6,LEN(B6)-FIND(" ",B6)),3),1)

That only works for single digit months and fails for months 10, 11 and 12. Try this instead (returns month as a real number)...

=0+LEFT(RIGHT(A1,3),2)

Dont understand, how to get, Year...I.e. "03"

=RIGHT(B6,3)-1

Im using this, but this giving me an error.."#Value!"

Give this a try (returns year as TEXT in order to preserve the leading 0 that you show you wanted)...

=LEFT(RIGHT(A1,3),2)

 

[pgc01]

For

NOTS0021CA (09/07)

try:

=-("("&RIGHT(B6;3))

to get the year as a number, in this case: 7

 

[VBABEGINER]

Pretty nice...to get the year..i.e. 03

but how do i get month from..."NOTS0021CA (09/03)"....month is "9"

Pls revert

That only works for single digit months and fails for months 10, 11 and 12. Try this instead (returns month as a real number)...

=0+LEFT(RIGHT(A1,3),2)




Give this a try (returns year as TEXT in order to preserve the leading 0 that you show you wanted)...

=LEFT(RIGHT(A1,3),2)

 

[VBABEGINER]

Thanks fro reply PG..Looks something..super..

For

NOTS0021CA (09/07)

try:

=-("("&RIGHT(B6;3))

to get the year as a number, in this case: 7

 

[Rick Rothstein]

Pretty nice...to get the year..i.e. 03

but how do i get month from..."NOTS0021CA (09/03)"....month is "9"

Not sure how it happened, but I had ended up posting the same formula twice in my previous message. Here is the month number formula I meant to post...

=0+LEFT(RIGHT(A1,6),2)

 

[VBABEGINER]

Thank You..Rick sir.

Rick Sir,

I need one expert help please...

Only my 04-05 types are pending. I need your expert help.


Output for this..
EP 1051-CA...in col A
7/1/2013......in col B

EP 1051-CA (07 13)

<colgroup><col></colgroup><tbody>
</tbody>

Output for this..
EPP-POL.......in col A
4/1/2010......in col B

EPP-POL-0410

Output for this..
HC0005.........in col A
12/1/2012......in col B

HC00051212

Output for this..
HC70010.........in col A
6/1/2005......in col B

HC70010605

I need in this format...could you please give me code for above types..please..

Not sure how it happened, but I had ended up posting the same formula twice in my previous message. Here is the month number formula I meant to post...

=0+LEFT(RIGHT(A1,6),2)

 

[Rick Rothstein]

I need one expert help please...

Only my 04-05 types are pending. I need your expert help.

Output for this..
EP 1051-CA...in col A
7/1/2013......in col B

Output for this..
EPP-POL.......in col A
4/1/2010......in col B

Output for this..
HC0005.........in col A
12/1/2012......in col B

Output for this..
HC70010.........in col A
6/1/2005......in col B

I need in this format...could you please give me code for above types..please..

Can all of these formats you keep posting all be mixed together within the column at the same time?