Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 20th 19, 09:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 53
Default Pick up specific marked text (one or more then one) out of a cell andcombine them into another

Dear,

As part of my earlier request (with thanks to Claus !), I have another small issue to solve. So again...... if somebody can help me out

In column A Cell 1 till the last, I've got combined text like;

[{"xxxx":356666,"Name":"AA1234"},{"yyyy":356667,"Na me":"BB3896"},{"yyyy":357633,"Name":"CC38901"}]

Whats the problem..... (and question).

All the values after name":" and thereafter between the quotes must be copied to the same row column B in a new Cell and separated with ";".

The above in A1 result in B1 then in;

AA1234;BB3896;CC38901


The macro picks up Name":" and takes the text thereafter till the next " copied this to column B and then looks to the next same condition. All found text must be copieed to the same row column B and combining the results with a split ";"


This looks a difficult one ?
I hope for a solution.

regards, Johan

  #2   Report Post  
Old March 20th 19, 10:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,660
Default Pick up specific marked text (one or more then one) out of a cell and combine them into another

Hi Johan,

Am Wed, 20 Mar 2019 14:49:28 -0700 (PDT) schrieb :

In column A Cell 1 till the last, I've got combined text like;

[{"xxxx":356666,"Name":"AA1234"},{"yyyy":356667,"Na me":"BB3896"},{"yyyy":357633,"Name":"CC38901"}]

Whats the problem..... (and question).

All the values after name":" and thereafter between the quotes must be copied to the same row column B in a new Cell and separated with ";".

The above in A1 result in B1 then in;

AA1234;BB3896;CC38901

The macro picks up Name":" and takes the text thereafter till the next " copied this to column B and then looks to the next same condition. All found text must be copieed to the same row column B and combining the results with a split ";"


try:

Sub Test()
Dim varTmp As Variant, varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long, j As Long
Dim n As Integer
Dim strTmp As String

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:A" & LRow)
For i = LBound(varData) To UBound(varData)
strTmp = ""
varTmp = Split(varData(i, 1), "Name"":""")
For n = 1 To UBound(varTmp)
strTmp = strTmp & ";" & Left(varTmp(n), InStr(varTmp(n), "}") - 1)
Next
ReDim Preserve varOut(j)
varOut(j) = Replace(Mid(strTmp, 2), """", "")
j = j + 1
Next
.Range("B1").Resize(UBound(varOut) + 1) = Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old March 20th 19, 11:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,660
Default Pick up specific marked text (one or more then one) out of a cell and combine them into another

Hi Johan,

Am Wed, 20 Mar 2019 23:15:06 +0100 schrieb Claus Busch:

Sub Test()
Dim varTmp As Variant, varData As Variant, varOut() As Variant


you can also do it with Regular Expressions:

Sub Test2()
Dim varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long
Dim n As Integer
Dim strTmp As String
Dim re, ptrn, Match, Matches

ptrn = "[A-Z]{1,2}[0-9]{1,6}"
Set re = CreateObject("vbscript.regexp")
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:A" & LRow)
For i = LBound(varData) To UBound(varData)
strTmp = ""
Set Matches = re.Execute(varData(i, 1))
For Each Match In Matches
strTmp = strTmp & ";" & Match
Next
ReDim Preserve varOut(UBound(varData) - 1)
varOut(i - 1) = Mid(strTmp, 2)
Next
.Range("B1").Resize(UBound(varOut) + 1) = Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Old March 21st 19, 04:47 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 53
Default Pick up specific marked text (one or more then one) out of a celland combine them into another

THANKS VERY VERY MUCH !!
The first one works oke.

Regards, Johan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pick a specific cell within a macro .... ?? Edi Excel Discussion (Misc queries) 4 March 26th 10 10:49 AM
Pick up some text in a cell Elton Law[_2_] Excel Worksheet Functions 9 April 27th 09 04:25 PM
Marking cell; totaling all # between marked cell & next marked cel Marley Excel Worksheet Functions 4 February 26th 09 01:26 AM
pick from 'drop down list' OR enter text in cell David Excel Worksheet Functions 2 December 13th 06 10:05 AM
Create a pick list to use to go to a text cell in Excel Pegita Excel Worksheet Functions 1 June 23rd 05 07:24 PM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017