Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

Extracting Specifc Text from Excel String (In, Out Values)


I copied some data from report and pasted into the excel the lines look something like this.

[Random Alphanumeric Chars of Varying Lengths] In=323 Out=69540 [Random Alphanumeric Chars of Varying Lengths].

What I am looking to do is extract the 'In' and 'Out' values into to separate columns.

I am using the following formula's but it doesn't work at all, all I am getting is #VALUE and I don't understand why.

=REPLACE(REPLACE(A1,FIND(" ",A1,FIND("In=",A1)),LEN(A1),""),1,FIND("In=",A1)+2,"")
=REPLACE(REPLACE(A1,FIND(" ",A1,FIND("Out=",A1)),LEN(A1),""),1,FIND("Out=",A1)+3,"")

Any ideas?
1 Solution
Naresh PatelTraderCommented:
I am getting values as per your formula 323 & 69540.

I don't seem any fault.

Naresh PatelTraderCommented:
just one try - change format to Text & then apply formula for that range.

Are  there are characters after Out=69540?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Glenn RayExcel VBA DeveloperCommented:
I tested several random strings with " In-323 Out=69540 " inside them and the formulas work for me.  So long as there is a space before "In" and after the value for Out, it looks like it should work.  

Maybe there are non-printable characters in the source string?  Try a TRIM and/or CLEAN function on it first and see if that resolves the issue.

Try this one with number format, if it is working, then change the text format.
Glenn RayExcel VBA DeveloperCommented:
Could you provide a sample file with some of the lines you pasted?  Maybe we can replicate the issue and resolve.

ZackGeneral IT Goto GuyAuthor Commented:
Cheers that was it, I had hyperlink into source column and that was causing the error.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now