separate text from column

Julio Jose
Julio Jose used Ask the Experts™
on
I have one column A many row data, how to use formula to get the output in column B and C?

Image-767.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naresh PatelFinancial Adviser

Commented:
instead of formula better to use Text To Column Function of excel is good.

Tab - Data - Data Tools - Text To column

Thanks

Author

Commented:
I need to get the selected string after "remote from" and "Username"
Naresh PatelFinancial Adviser

Commented:
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
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Naresh PatelFinancial Adviser

Commented:
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
Excel VBA Developer
Top Expert 2014
Commented:
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

Author

Commented:
Thanks
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You're welcome.  I'm glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial