[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Parse out field

I have the following fields that I need to pull data out of.  

PERCEPTIVE SOFTWARE, I#9005197267
CAREMEDIC, MEDICARE RT SUBSCRIPTION I# 5545926
RECLASS A007265 I#3076 TO BLDG IMP, MAR 2013
RECLASS A007265 I#3149 TO BLDG IMP, MAR 2013
RECLASS A007265 I#3154 TO BLDG IMP, MAR 2013


I want to pull the data for I#.  so the results would look like this:

9005197267
5545926
3076
3149
3154
0
Scotto111
Asked:
Scotto111
  • 7
  • 4
  • 3
1 Solution
 
Scotto111Author Commented:
I have planned to make 2 formulas and combine them together.  This will allow for the 2 types of examples to use different formulas.
0
 
Saqib Husain, SyedEngineerCommented:
This takes care of all the given cases

=REPLACE(TRIM(REPLACE(A9,1,FIND("I#",A9)+1,"")),FIND(" ",TRIM(REPLACE(A9,1,FIND("I#",A9)+1,""))&" "),9999,"")
0
 
Scotto111Author Commented:
I converted this to Monarch format:
=REPLACE(TRIM(REPLACE([Description],1,Instr("I#",[Description])+1,"")),Instr(" ",TRIM(REPLACE([Description],1,Instr("I#",[Description])+1,""))&" "),9999,"")

What is the &" "),9999 doing?

Monarch doesn't like the &.  Any ideas?  I know this isn't Monarch forum.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Can you upload a fake file with this?
0
 
Scotto111Author Commented:
text file? or monarch file?
0
 
Saqib Husain, SyedEngineerCommented:
Monarch
0
 
Scotto111Author Commented:
ssaqibh,
Too much work to clean the data and send it.  I am in a time crunch.  Really appreciate your help.  

I have 2 types of data in one field:
Type 1:
V# M667938 I# 9005044233 F# EMC R# 0000318829 Remit to: PERCEPTIVE SOFTWARE
V# M667938 I# 9005197267 F# EMC R# 0000318829 Remit to: PERCEPTIVE SOFTWARE
V# M668008 I# 10727008 F# EMC Vendor: KRONOS
V# M000379 I# 5545926 F# EMC Vendor: CAREMEDIC SYSTEMS, INC.
V# A001761 I# DUES13-H13022 F# EMC Vendor: CHA/CAHHS

Type 2:
PERCEPTIVE SOFTWARE, I#9005197267
CAREMEDIC, MEDICARE RT SUBSCRIPTION I# 5545926
RECLASS M000521 I#134914 PO#342533 TO EXP, MAR 13

For type 1 I have this working:
If([I#]>0,(Mid_XL([Description],[I#]+3,[F#]-[I#]-4)),"")

Can you use this to make a formula that will work on both?  Or even one that will work on Type 2 and I can combine them.
0
 
Saqib Husain, SyedEngineerCommented:
I have never used these tables so I might not be able to set it up appropriately. Can you just feed the above data in a new file and set it up accordingly?
0
 
Scotto111Author Commented:
0
 
Scotto111Author Commented:
I posted the data into a text file.  Not sure how to set up model in monarch to send with it.  Hope you can help.
0
 
byundtCommented:
& is the concatenation character in Excel. The following two formulas are equivalent:
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(A1,FIND("I#",A1)+2,99)) + " "," ",REPT(" ",99)),20))
=TRIM(LEFT(SUBSTITUTE(CONCATENATE(TRIM(MID(A1,FIND("I#",A1)+2,99)), " ")," ",REPT(" ",99)),20))

REPT is a function that returns multiple copies of specified text, in this case the space character.

Another way of avoiding concatenation uses the IFERROR function:
=TRIM(MID(A1,FIND("I#",A1)+2,IFERROR(FIND(" ",A1,FIND("I#",A1)+3)-FIND("I#",A1)-2,99)))
0
 
byundtCommented:
Could you nest If functions within the Monarch string?
If([I#]>0,(Mid_XL([Description],[I#]+3,If([F#]>0,[F#]-[I#]-4),99)),"")
0
 
Scotto111Author Commented:
It can't do nested Ifs.  Can you make 2 separate fields for me - for each type of data?
0
 
byundtCommented:
I don't know the syntax of Monarch formulas, but the following seems to me as though it would work:
Val(ltrim(lsplit(Description,2,"I#",2)))

Take the Description field and split into two parts at I#. Return the second part.
Remove any leading spaces from the previous returned value
Convert the previous returned value into a number. Conversion will stop at the first character that isn't a leading sign or digit.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now