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

separate text from column

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
Julio Jose
Asked:
Julio Jose
  • 3
  • 2
  • 2
1 Solution
 
Naresh PatelTraderCommented:
instead of formula better to use Text To Column Function of excel is good.

Tab - Data - Data Tools - Text To column

Thanks
0
 
Julio JoseAuthor Commented:
I need to get the selected string after "remote from" and "Username"
0
 
Naresh PatelTraderCommented:
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Naresh PatelTraderCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Julio JoseAuthor Commented:
Thanks
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  I'm glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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