Solved

separate text from column

Posted on 2016-08-19
7
51 Views
Last Modified: 2016-08-31
I have one column A many row data, how to use formula to get the output in column B and C?

Image-767.png
0
Comment
Question by:Julio Jose
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
instead of formula better to use Text To Column Function of excel is good.

Tab - Data - Data Tools - Text To column

Thanks
0
 

Author Comment

by:Julio Jose
Comment Utility
I need to get the selected string after "remote from" and "Username"
0
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
Assuming Your Data is in Cell A1

Than Formula is
=MID(A1,FIND(":",A1)+2,2)

Open in new window

=RIGHT(A1,LEN(A1)-SEARCH(":",A1,FIND(":",A1)+1))

Open in new window


Thanks
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:itjockey
Comment Utility
Assuming you want only two characters From remote from.

=MID(A1,FIND(":",A1)+2,2)

Open in new window

=RIGHT(A1,LEN(A1)-(FIND(":",A1,FIND(":",A1)+1)+1))

Open in new window

See Attached
Text.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
Presuming that the markers "Userform : " and "Remote From : " are constant (that is, they both exist in every source line and both also include the space-colon-space after each), the following two formulas can be used to extract the Username and the Remote From (IP address).  This will work regardless of the length or composition (i.e., any punctuation) in the Username.

Assuming first data is in cell A2...
Username
B2: =MID(A2,FIND("Username",A2)+11,FIND("Remote From",A2)-FIND("Username",A2)-12)

Remote From
C2: =MID(A2,FIND("Remote From : ",A2)+14,15)

See the attached workbook for an example.

Regards,
Glenn
EE_ParseString.xlsx
1
 

Author Closing Comment

by:Julio Jose
Comment Utility
Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
You're welcome.  I'm glad I could help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now