Hallo,
ich wollte in Exel das Wort Weihnachten mit der Funktion =Teil($A$1;1;1) Teilen nur das Problem ist das ich nur ein Buchstabe in der Zelle habe und ich aber das Komplette Wort nach unten Geteilt haben.
PS: Bin ein kompletter Anfänger
Hallo,
ich wollte in Exel das Wort Weihnachten mit der Funktion =Teil($A$1;1;1) Teilen nur das Problem ist das ich nur ein Buchstabe in der Zelle habe und ich aber das Komplette Wort nach unten Geteilt haben.
PS: Bin ein kompletter Anfänger
This is how Google translated your post.
I don't understand from the translation.
12-11-2015 12-30-54 AM.jpg
In English, I think you need these formulae:
Formula:Please Login or Register to view this content.
With Weihnachten in A1, those will give you:WI don't know what MID is in German, but if you open the attached file, it should automatically translate it (and put semicolons ; for the commas , ).
e
...
n
File: Weinachten single letters _ for SpieleTitan.xls
Edit: I mis-spelled Weihnachten in the file, but otherwise it works...
Last edited by Aardigspook; 12-12-2015 at 08:01 AM. Reason: Note mis-spelling in file
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Hi,
http://de.excel-translator.de/teil/
confirms that DE:
Teil
=ENG:
Mid
Try extended version of Aardigspook formula:
Formula:Please Login or Register to view this content.
(once you open the attachment you will see it's German version)
You can copy it down to several cells (I know that in German there could be some pretty long words like: Donaudampfschiffahrtselektrizitätenhauptbetriebswerkbauunterbeamtengesellschaft, etc.
MfG,
Kaper
@Kaper: nice amendment - I wanted to use ROW somehow, but couldn't get it to be generic in the short time I had.
Hello. Hallo
Oh,
I missed this German one while i was away. As I am practicing my “Row Column ( ZEILE SPALTE ) generic in formula” stuff just now, and i can “read” in native German, thought i would make a quick contribution....because.. amongst other things..... The Google translator is not too good, especially when technical or Excel terms and Phrases come in.
( The site for formulas translation kaper mentioned is very good. The basic translator there I use a lot to translate a formula.
http://de.excel-translator.de/translator/
Also if the OP Posts uses a screen shot tool , like that from RoryStomper ( RIP - poo Wonk!! )
http://www.excelforum.com/suggestion...ml#post4259669
_____..... then it is already translated for us.
Also a trick i learnt just recently,
http://www.mrexcel.com/forum/excel-q...ml#post4367502
______ ... run a macro recording when writing the formula. ( You get only the R1C1 Format, but otherwise you get a translation into English.) For Example
I wrote:
____=TEIL($A$1;1;1)
And got
____ActiveCell.FormulaR1C1 = "=MID(R1C1,1,1)"
OR
I wrote
___ =TEIL(A1;1;1)
And got
ActiveCell.FormulaR1C1 = "=MID(R[-13]C[-7],1,1)"____( I was typing it in Cells(14, 8) ___H14____)
Also, Aardigspook, i can confirm that Formulas seem always to come up translated to suit the German Excel. Or Visa Versa. That is one thing that makes attachments useful for me......)
So Not sure if I fully understand yet what the OP wants, but I can tell you exactly what he said:
Maybe he has a typo. I speak German fluently all the time. He is saying that he has the word divided up already downwards. So it is not at all clear what he wants. He is not saying where he wants the output or if he has already something similar to what you guys did for him. So it really does not make perfect sense what he says.“ ....I wanted to split the word Weihnacten using the Excel Function =MID($A$1,1,1) . The problem is that I only have one character in the Cell and have the whole word divided up downwards.
P.s. I am a complete beginner”
But I am practicing me “Row Column ( ZEILE SPALTE ) generic in formula” stuff, so it was nice to play with Kapers Formula
=WENN(LÄNGE($A$1)>=ZEILE(1:1);TEIL($A$1;ZEILE(1:1);1);"")
=IF(LEN($A$1)>=ROW(2:2),MID($A$1,ROW(2:2),1),"")
_ ..............................................
So Anyways
Another variation. The OP may have explained himself badly in any language! Or rather what he said in German could be read as meaning to say something else. Just like in English ( and i think across the pond also )... you say you are going “to take a Dump”. But you are not taking anything anywhere. So I would read as one possibility in native German it to mean that he “sees only” one Character in the cell because in the cell the Word is written like this.
____X
____m
____a
____s
In my Excel I only see X _____( BTW. I put that in by copying
____X
____m
____a
____s
From MS WORD. I could not figure out how to do that in Excel. This did not work –
if anyone knows how to do that I would be interested......)Please Login or Register to view this content.
Anyways . If my guess is correct the problem is the extra vbCr which gives a length of about 7 or 8 for Xmas, depending on exactly what i copied from MS WORD
_ So this could be one more solution:
Screenshot of values:
( I only see X in Cell(1, 1) __( unless change the Cell height )
Using Excel 2007
Row\Col A B C D E F 1 X
m
a
s
X m a s 2 X m a s 3 8
AardiAlanKap
Screenshot of Formulas
(__I typed in
__A3__ =LÄNGE($A$1)
__C1__ =TEIL($A$1;1;1)___(and manually did other formulas to the right for the rest)
__C2__ =WENN((LÄNGE($A$1)/2)>=SPALTE(A:A);TEIL($A$1;((SPALTE(A:A)*2)-1);1);"")___(and was practicing me “Row Column ( ZEILE SPALTE ) generic in formula” stuff, so i did it so you can drag it across)
Row\Col A B C D E F 1 X
m
a
s
=MID($A$1,1,1) =MID($A$1,3,1) =MID($A$1,5,1) =MID($A$1,7,1) 2 =IF((LEN($A$1)/2)>=COLUMN(A:A),MID($A$1,((COLUMN(A:A)*2)-1),1),"") =IF((LEN($A$1)/2)>=COLUMN(B:B),MID($A$1,((COLUMN(B:B)*2)-1),1),"") =IF((LEN($A$1)/2)>=COLUMN(C:C),MID($A$1,((COLUMN(C:C)*2)-1),1),"") =IF((LEN($A$1)/2)>=COLUMN(D:D),MID($A$1,((COLUMN(D:D)*2)-1),1),"") 3 =LEN($A$1) =IF(LEN($A$1)>=COLUMN(2:2),MID($A$1,ROW(2:2),1),"")
AardiAlanKap
I may not have guessed what the OP wanted... and he probably will never get back anyway but like I saw recently.....
____”hey, I probably learnt something from the exercise so all is not lost.“
http://www.excelforum.com/suggestion...ml#post4253283
Alan
Bavaria
Germany
Last edited by Doc.AElstein; 12-19-2015 at 09:57 AM. Reason: Put a Dump In ... and Took a Wonk..... Poo
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks