+ Reply to Thread
Results 1 to 6 of 6

=Teil() Problem

  1. #1
    Registered User
    Join Date
    12-10-2015
    Location
    German
    MS-Off Ver
    2013
    Posts
    2

    Smile =Teil() Problem

    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

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,265

    Re: =Teil() Problem

    This is how Google translated your post.
    I don't understand from the translation.

    12-11-2015 12-30-54 AM.jpg

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: =Teil() Problem

    In English, I think you need these formulae:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    With Weihnachten in A1, those will give you:
    W
    e
    ...
    n
    I 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 , ).
    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

    Recently moved house, internationally, during COVID (!) 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.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,255

    Re: =Teil() Problem

    Hi,

    http://de.excel-translator.de/teil/
    confirms that DE:
    Teil
    =ENG:
    Mid

    Try extended version of Aardigspook formula:
    Formula: copy to clipboard
    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

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: =Teil() Problem

    @Kaper: nice amendment - I wanted to use ROW somehow, but couldn't get it to be generic in the short time I had.

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: =Teil() Problem

    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:

    “ ....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”
    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.

    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 –
    Please Login or Register  to view this content.
    if anyone knows how to do that I would be interested..
    ....)

    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 )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Macro - Copy & Paste (Font) problem & Delete Last Added Rows problem
    By LennartB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 06:58 AM
  2. Update Values problem and a Value Selection Problem----Please Help.
    By TrivialT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2013, 02:43 PM
  3. Replies: 6
    Last Post: 10-20-2013, 07:16 PM
  4. input box problem, cell filtering problem, result display while locking sheet
    By croozin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2013, 08:52 AM
  5. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  6. Replies: 2
    Last Post: 01-22-2013, 07:09 AM
  7. Started out as an Access problem. Now an Excel problem
    By RobertM in forum Excel General
    Replies: 2
    Last Post: 04-26-2006, 02:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1