Formula help copy text to new cell

[raj192]

Hi, im trying to make a formula that lets me do this:
If A Cell e.g. A2 contains R and M. copy R to cell B2 and Copy M to C2
and the same for A3, copy all 3 letter to separate cells

A1=R
A2=R, M
A3=R,M,L
A4=M

Thanks for looking

[JohnSUN-Pensioner]

Welcome to the forum!
Have you tried the menu Data - Text to Columns?

[raj192]

Thanks you for the quick reply,
However, my spreedsheet contains lots of date, which is imported from sheet 1 to sheet 2.
on sheet 2 to I need to copy the cell which contains more then 1 letter to a new cell.
All this date is then exported to xml.
I'v got the xml sorted just need some help to create formula.

[JohnSUN-Pensioner]

Unfortunately, I have not yet learned how to insert videos from YouTube

[raj192]

Once again thank you for taking the time to look at this, but this is not what i am looking for.
I have already copied the info from sheet 1 to sheet 2. this i know how to do.

The text to column is not the solution. as
1. Need to the keep the current values in current cell but just need to a copy of the letter in new cell
2. Needs to be a formula which i can reuse for other cell, but i will change the cell column number and row letter my self.
3. Text to column only separate the letter to the next row i need to move it to a select cell.
4. Text to column does not copy but move the letter. I still need the cell to keep the values.

I need a formula that works like this:

Cell AB2 = QS
If(AB2 contains letter Q then copy this to a new cell AG2)
if(AB2 contains letter S) then copy this to new cell AF2)

Regards

[JohnSUN-Pensioner]

I have already copied the info from sheet 1 to sheet 2. this i know how to do.

OK, I understood this from your past posts. I'm included it to the film for those who don't know.

1. Need to the keep the current values in current cell but just need to a copy of the letter in new cell

No problems! Just copy column A to column B once more and use TextToColumn for it

2. Needs to be a formula which i can reuse for other cell, but i will change the cell column number and row letter my self.

You can use this tool for any ranges. And even for a single cell. Simply by selecting with Shift+Click

3. Text to column only separate the letter to the next row i need to move it to a select cell.

Sorry, my English is far from perfect. I do not understand this requirement. Want to be formulated differently?

4. Text to column does not copy but move the letter. I still need the cell to keep the values.

You say this in paragraph 1, didn't it?

I need a formula that works like this:
Cell AB2 = QS
If(AB2 contains letter Q then copy this to a new cell AG2)
if(AB2 contains letter S) then copy this to new cell AF2)

I am very confused! Do you mean that the separator between the individual letters is not? And each letter should fall into a particular column?
Can you attach to your next post a sample file? Draw a few arrows that show what and where to be copied. I hope this will help us better understand each other.

[raj192]

Ok will attach a sample just need to clean it up a bit.
I have 1 more question.
if (c1= P output the word "size" in cell b1) but if (c1= blank output = blank in cell b1)

This current forumla output the word "size" if the cell contains text inside the cell, if there is no text it output "False"

=IF(ISTEXT(DW12);"size")

I want the cell to be left blank AND not output "False" if there is no text inside the cell.
Thanks will upload sample

[karolus]

=IF(ISTEXT(DW12);"size";"")

Karo

[raj192]

=IF(ISTEXT(DW12);"size";"")

Karo

THANK YOU

[raj192]

I have attached a sample.

the coloured cell show the letters being copy into new cells

e.g B4= P,L,N cell H4 check to see if B4 contains P if so copy P to B4.
Then cell K4 check to see if B4 contains L if so copy to K4
I hope this helps sorry about the bad illustration

[JohnSUN-Pensioner]

Oh! So it is much clearer!
There remains a question. In your example, "Cell AB2 = QS" in the text no commas.
It's just a erratum? All letters are always separated by a comma?

PS. In your example B6=P but H6=v. Once more typo?

[raj192]

Sorry yes typo

Oh! So it is much clearer!
There remains a question. In your example, "Cell AB2 = QS" in the text no commas.
It's just a erratum? All letters are always separated by a comma?

There will also be a comma.

PS. In your example B6=P but H6=v. Once more typo?

yes typo
Just to give u an insight.

The letters are refine to rings size.
J,K,L,M,N,O,P,Q,R,S

I then need to separate them in to seperate cell to create varitation which is related to xml items

Thank you for your help

[JohnSUN-Pensioner]

Well, my friend, I understand.
This can be done only functions Calc. But the formula will be very cumbersome.
It is easier to do this using a simple macro.

Edit: PS

The letters are refine to rings size.
J,K,L,M,N,O,P,Q,R,S

Ten letters only?!!
I was a little complicated macro. This version will not return #N/A

[raj192]

Yes ten letters only.

I have run the macro but all i see is #VALUE, this is also appearing in fields which should remain blank.
once the cell is filled with the Letter the cell to the right will have the word size next to it.

I have this forumula instead of outputing the number 1, I want is to out the letter R
=ISNUMBER(SEARCH("R";DX2))+ISTEXT(0)

Thanks

[JohnSUN-Pensioner]

I have run the macro but all i see is #VALUE, this is also appearing in fields which should remain blank.

You can not get a set of strings?

The result of the function for me

Perhaps this feature of the Mac? I don't understand the causes of this error

once the cell is filled with the Letter the cell to the right will have the word size next to it.

Next? I put this word in previous cell

I have this forumula instead of outputing the number 1, I want is to out the letter R
=ISNUMBER(SEARCH("R";DX2))+ISTEXT(0)

Forgive me for my dullness.
What does it mean in this formula DX2? And ISTEXT(0)?
All right. Assume that we have a line of "P, L, N"
What we want to get out of it?

Code: Select all

| Size | P | 1 | Size | L | 2 | Size | N | 3 | 

or

Code: Select all

| Size | P | 1 | Size | L | 3 | Size | N | 5 | 

or something else?

[raj192]

JohnSUN-Pensioner apologise,
I had the macro set to high and that why this was not working for me, its now working, so thank you very much for you time and help.
I need to test this in my main spread sheet with contain 50 column but before i do that i need to make a few small changes.

I'll upload a the final version of what i need, basically after the Letter is copied into the cell, the next 3 cell will also contain information, currently i can't edit the cell next to Letter "YOU CAN'T CHANGE ANY PART OF ARRAY".

I dont need you to put the info in the cell i just need to be able to edit the cells.
I will upload sample so you understand better.

Thanks once again

[JohnSUN-Pensioner]

...its now working, so thank you very much for you time and help.

I'm glad that I help to you.

...i need to make a few small changes.

Yes, I noticed some trouble in this solution too.

...basically after the Letter is copied into the cell, the next 3 cell will also contain information...

Do you mean that?

Code: Select all

P,L,N | | | |(Some empty cells for indent - how much?)| P |(1)|(2)|(3 - three empty cell after each letter)| L |(1)|(2)|(3)| N |

I will upload sample so you understand better.

OK, I'll be waiting

[raj192]

Yes

Code: Select all

P,L,N | | | |(Some empty cells for indent - how much?)| P |(1)|(2)|(3 - three empty cell after each letter)| L |(1)|(2)|(3)| N |

Let me expain a bit more about the process.

So each ring size has a set of attributes, when i export to xml it look like this:
<items>
<variation>
<attributeName>Size</attributeName>
<attributeValue>S</attributeValue>
<price>10.00</price>
<sku>PT950RIWBCL1</sku>
<quantity>1</quantity>
</variation>
</items>

But in the Calc it look like this:
variation | attributeName | attributeValue | price | sku | quantity | /variation
size R 10.00 PT950RIWBCL1 1

Attributes will also be the same. The cell numbers below are examples
Price will be copied from a cell A2,
SKU will be copied from cell C2 will uses a formaula to add 1 end of sku number e.g.=IF(ISTEXT(DX2);C2&1;""). the SKU is unique for each ring size, that why I use the formula .
Quatity is also copied from a cell e.g F2.
Variation and /varition are open and close tags which are blank.

Does this make sense so far?

I have imported an xml exporter to calc. this produces my xml, but i still need to do a find and replace as some of the child tags are not correct.

Do you know much about XML and XSLT? The reason I ask is becuase if you are able to modify XSLT to output the data in the correct format, then i can change the Calc layout for the cell,

e.g. can remove the Variation and /varition cell from the spreedsheet as the XSLT can produces these in the export of XML.

Will upload a sample in 20 mins
Regards

[raj192]

i'v attached a sample.

Thanks