Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

separate text from column

Posted on 2016-08-19
7
Medium Priority
?
63 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:Naresh Patel
ID: 41762519
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
ID: 41762530
I need to get the selected string after "remote from" and "Username"
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41762541
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
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.

 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41762543
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 2000 total points
ID: 41765822
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
ID: 41774817
Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41779217
You're welcome.  I'm glad I could help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

876 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