CODEWITHSUNDEEP
parsingexcelvba
thedev
thedev

Reputation: 2886

Excel parse cell value

I have placed the following in cell A1:

"a lot of text marker: xxx some more text"

I would like to copy the xxx value into cell A2.

Any suggestions on how this could be done?

Thanks

Upvotes: 1

Views: 10796

Answers (3)

JimmyPena
JimmyPena

Reputation: 8754

If you wanted a VBA solution, this worked for me using your sample input:

Function GetValue(rng As Excel.Range) As String
  Dim tempValue As String
  Dim arrValues() As String
  ' get value from source range
  tempValue = rng.value
  ' split by ":" character
  arrValues = Split(tempValue, ":")
  ' split by spaces and take the second array element
  ' because there is a space between ":" and "xxx"
  GetXXXValue = Trim$(Split(arrValues(1), " ")(1))
End Function

To use, put this code into the sheet module (see Where do I paste the code that I want to use in my workbook for placement assistance) and then put the following into cell A2:

=GetValue(A1)

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

Just my two cents. Find() is case sensitive so if the text in A1 is

"a lot of text Marker: xxx some more text"

Then Find will give you an error.

You can use Search() in lieu of FIND()

=MID(A1, SEARCH("marker: ",A1) + LEN("marker: "), 3)

Also depending upon your regional settings you might have to use ";" instead of ","

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

=MID(A1, FIND("marker:",A1) + LEN("marker:"), 4)

I am assuming that the xxx (per your example) is 3 characters long and a space is present between "marker:" and "xxx".

Upvotes: 2

Related Questions