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

startzc

New Member
Joined
Oct 1, 2014
Messages
4
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.

[BLDG: 90210; RM: 108; ORGANIZATION: UNKNOWN; USER: $svc.FTEV.fsmpss; LAST UPDATE: 08/13/2014]

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

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,804
Office Version
  1. 365
Platform
  1. Windows
This works on your example.

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

startzc

New Member
Joined
Oct 1, 2014
Messages
4
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

Board Regular
Joined
Aug 1, 2012
Messages
134
<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

New Member
Joined
Oct 1, 2014
Messages
4

ADVERTISEMENT

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

startzc

New Member
Joined
Oct 1, 2014
Messages
4
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
Try this formula to extract the number after „RM”:

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

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top