Excel cell containingg several values separated by a comma VBA to extract the values

Question

Hello 

im a bit new to VBA so here goes, i have a cell that contains several valies separated by a comma

i.e a,b,c 

i need to extract the first  values 'a' and place that in a cell then go to the next value 'b' and put that into another cell

field 1   field 2  field3   

1           a           2

2           b           3 

any ideas....

Answer 1

Run the code below into a new file.

BTW, a ListBox returns also a String, so you can use that as input for the code below.

Where the output goes... thousand ways to skin a cat.

Andreas.

Sub Test()
  Dim S As String
  Dim Data
  Dim i As Integer
  
  'This is the input
  S = "a,b,c"
  'Split the string by comma and get an array
  Data = Split(S, ",")
  'Loop through the array
  For i = 0 To UBound(Data)
    'Write into row 1
    Range("A1").Offset(, i).Value = Data(i)
  Next
End Sub

Answer 2

Where b and c would appear? If you can post image that would be better as it is very difficult to understand pasted table.

Answer 3

Hi themase100

Greetings! I am Vijay, an Independent Advisor.

1. What is field 3 which has value 2 and 3
2. Do you have such kind of values in cells in a column

Hi Vjay 

thanks for the reply so basically i have a tool which I'm using to pull data from  various tables (as below) this all works fine, i have list box which the user will be populating their options(multiple options) im running a bit of vba to copy (the form below) from a template for the amount of choices in the list box so if the user chooses 3 option then the below form is copied 3 time etc , what i need is field 1 (below) to be populated with 1 of the list box items and the second form with the second etc, hope this helps

List-box

A,B,C 

field	field 1	Date	Cruise Name	Nights		Saver Discount		Cabins Left		Parking	Coach
#N/A	'A'	#N/A	#N/A	#N/A		67		#N/A		Cost	Offer	CostSingle	Offer
																	#N/A		#N/A
	GradeOptions		GradeOptions		GradeOptions		GradeOptions		GradeOptions
field	Grade	Brochure Fare	Saver Fare		Grade	Brochure Fare	Saver Fare		Grade	Brochure Fare	Saver Fare		Grade	Brochure Fare	Saver Fare		Grade	Brochure Fare	Saver Fare
#N/A		#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A
#N/A		#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A
#N/A		#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A			#N/A	#N/A
Other Information

Answer 4

Hi themase100

Greetings! I am Vijay, an Independent Advisor.

1. What is field 3 which has value 2 and 3
2. Do you have such kind of values in cells in a column