Extract Data from middle with variable length strings left and right?

[startzc]

I have been going through every command I can find and trying to tweak it to extract 2 separate sets of data into new columns. I was able to get half but the other would not work.

I need to extract just the BLDG# "90210" in one column, not always 5 characters but the "BLDG: " and the following semi-colon are always there.

In teh second column I need just the RM# whcih can vary from 1 to 6 characters in length and also always includes the "RM: " and following semi-colon.

[TABLE="width: 567"]
<colgroup><col></colgroup><tbody>[TR]
[TD][BLDG: 90210; RM: 108; ORGANIZATION: UNKNOWN; USER: $svc.FTEV.fsmpss; LAST UPDATE: 08/13/2014]
[/TD]
[/TR]
</tbody>[/TABLE]

Alternatively if there is a way to just import that column into another spreadsheet and automatically delimit it I could easily make this work also.

Background info:
The spreadsheet this is in is being extracted fro Active Directory using a VBS script. This spreadsheet then gets imported into an Access database to track/manage assets, including location as entered every 60 days by moronic users that like to screw up our whole system.

Thanks

 

[Norie]

This works on your example.

=TRIM(MID(LEFT(A1,FIND(";",A1)-1),7,LEN(A1)))

 

[startzc]

That worked for Bldg#. Thanks

I await something for the second set. I could get it to give me "RM: 128" but it would not take #s less than 3 digits or drop the "RM: " which I could work around if it would pull all the Rm#s at least.

 

[t0azt]

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">=RIGHT(LEN(A1)-x)Replace the x with the number of characters you want removed.Hope that helps!</code></pre>

 

[startzc]

No, there are varying lengths of characters in front of and after the data I need, but thank you.

 

[startzc]

Never-mind, but thank you for your help. Went a much easier way of just delimiting the column and then using =REPLACE to get rid of the lead characters. Now I have the data the way I need it.

 

[István Hirsch]

Try this formula to extract the number after „RM”:

=MID(A1,FIND("RM: ",A1)+4,FIND(",",A1,FIND("RM: ",A1))-FIND("RM: ",A1)-4)